Query to show XML output for hierarchical data

Question

Post reply

Query to show XML output for hierarchical data

Jampandu

Ten Centuries

Points: 1062
More actions
March 10, 2015 at 10:48 am

#298411

Hi Experts,
Could you please help me in selecting table data in hierarchical XML .
Here is the sample table DDL and data
Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int
,country_Name varchar(100)
,country_code varchar(10)
,country_surface_area varchar(100)
,State_id int
,State_Name varchar(100)
,State_code varchar(10)
,State_surface_area varchar(100)
)
INSERT INTO @continents
SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10111,'state1','st1','efg sq km' union all
SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10112,'state2','st2','efg sq km' union all
SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10113,'state3','st3','efg sq km' union all
SELECT 101,'continent1','xyz sq km',1012,'country2','cnt2','pqr sq km',10114,'state4','st4','efg sq km' union all
SELECT 101,'continent1','xyz sq km',1012,'country2','cnt2','pqr sq km',10115,'state5','st5','efg sq km' union all
SELECT 102,'continent2','aaa sq km',1013,'country3','cnt3','pqr sq km',10116,'state6','st6',null union all
SELECT 102,'continent2','aaa sq km',1013,'country3','cnt3','pqr sq km',10117,'state7','st7',null
select * from @continents
Here , One continent can have multiple countries and one country can have multiple states.
I need to display this data in hierarchical xml as per attached xml file.
<?xml version="1.0" encoding="UTF-8"?>
<Records>
<header>
<FileDate> YYYYDDMM </FileDate>
</header>
<body>
<continents>
<continent>
<continent_id>101</continent_id>
<continent_Name>continent1</continent_Name>
<continent_surface_area>xyz sq km</continent_surface_area>
<countries>
<country>
<country_id>1011</country_id>
<country_Name>country1</country_Name>
<country_code>cnt1</country_code>
<country_surface_area>abc sq km</country_surface_area>
<states>
<state>
<State_id>10111</State_id>
<State_Name>state1</State_Name>
<State_code>st1</State_code>
<State_surface_area>efg sq km</State_surface_area>
</state>
<state>
<State_id>10112</State_id>
<State_Name>state2</State_Name>
<State_code>st2</State_code>
<State_surface_area>efg sq km</State_surface_area>
</state>
<state>
<State_id>10113</State_id>
<State_Name>state3</State_Name>
<State_code>st3</State_code>
<State_surface_area>efg sq km</State_surface_area>
</state>
</states>
</country>
<country>
<country_id>1012</country_id>
<country_Name>country2</country_Name>
<country_code>cnt2</country_code>
<country_surface_area>pqr sq km</country_surface_area>
<states>
<state>
<State_id>10114</State_id>
<State_Name>state4</State_Name>
<State_code>st4</State_code>
<State_surface_area>efg sq km</State_surface_area>
</state>
<state>
<State_id>10115</State_id>
<State_Name>state5</State_Name>
<State_code>st5</State_code>
<State_surface_area>efg sq km</State_surface_area>
</state>
<state>
<State_id>10113</State_id>
<State_Name>state3</State_Name>
<State_code>st3</State_code>
<State_surface_area>efg sq km</State_surface_area>
</state>
</states>
</country>
</countries>
</continent>
<continent>
<continent_id>102</continent_id>
<continent_Name>continent2</continent_Name>
<continent_surface_area>aaa sq km</continent_surface_area>
<countries>
<country>
<country_id>1013</country_id>
<country_Name>country3</country_Name>
<country_code>cnt3</country_code>
<country_surface_area>pqr sq km</country_surface_area>
<states>
<state>
<State_id>10116</State_id>
<State_Name>state6</State_Name>
<State_code>st6</State_code>
<State_surface_area> </State_surface_area>
</state>
<state>
<State_id>10117</State_id>
<State_Name>state7</State_Name>
<State_code>st7</State_code>
<State_surface_area> </State_surface_area>
</state>
</states>
</country>
</countries>
</continent>
</continents>
</body>
</Records>
Thanks in Advance!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

See if this helps

SELECT t1.continent_id,

t1.continent_Name,

t1.continent_surface_area,

(SELECT t2.country_id,

t2.country_Name,

t2.country_code,

t2.country_surface_area,

(SELECT t3.State_id,

t3.State_Name,

t3.State_code,

t3.State_surface_area

FROM @continents t3

WHERE t3.country_id = t2.country_id

FOR XML PATH('state'),ROOT('states'),TYPE)

FROM @continents t2

WHERE t2.continent_id = t1.continent_id

GROUP BY t2.country_id,

t2.country_Name,

t2.country_code,

t2.country_surface_area

FOR XML PATH('country'),ROOT('countries'),TYPE)

FROM @continents t1

GROUP BY t1.continent_id,

t1.continent_Name,

t1.continent_surface_area

FOR XML PATH('continent'),ROOT('continents'),TYPE;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jampandu Ten Centuries Points: 1062 More actions · Answer 2

Thanks a lot Mark for providing query and it helps.

Also Could you please add below mentioned <Records>,<header>and </body>elements to the same query.

<?xml version="1.0" encoding="UTF-8"?>

<FileDate> YYYYDDMM </FileDate>

</header>

<body>

.......Here,I will use query provided by you

</body>

</Records>

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

SELECT

(SELECT CONVERT(CHAR(8),GETDATE(),112) AS "FileDate" FOR XML PATH('header'),TYPE),

(

... my previous query here...

) AS "body"

FOR XML PATH('Records'),TYPE;

As far as I know you have to add the encoding '<?xml version="1.0" encoding="UTF-8"?>' in manually - cast the XML result to NVARCHAR(MAX) and concatenate strings.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jampandu Ten Centuries Points: 1062 More actions · Answer 4

Jampandu

Ten Centuries

Points: 1062

March 11, 2015 at 9:12 am

#1783329

Perfect Mark, Thank you very much...!!!

Jampandu Ten Centuries Points: 1062 More actions · Answer 5

Hi Mark,

Is this possible to add counts at each level in xml like below .

<count>xx</count>

..........

...........

<count>yy</count>

..........

...........

<count>zz</count>

<state>

..........

...........

Here,

xx --> number of continents

yy -->number of countries under continent

zz -->number of states under country

Thanks in Advance

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

Quick modification to Mark's code adding the counts

😎

USE tempdb;

GO

SET NOCOUNT ON;

Declare @continents Table

(

id int identity (1,1)

,continent_id int

,continent_Name varchar(100)

,continent_surface_area varchar(100)

,country_id int

,country_Name varchar(100)

,country_code varchar(10)

,country_surface_area varchar(100)

,State_id int

,State_Name varchar(100)

,State_code varchar(10)

,State_surface_area varchar(100)

)

INSERT INTO @continents

SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10111,'state1','st1','efg sq km' union all

SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10112,'state2','st2','efg sq km' union all

SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10113,'state3','st3','efg sq km' union all

SELECT 101,'continent1','xyz sq km',1012,'country2','cnt2','pqr sq km',10114,'state4','st4','efg sq km' union all

SELECT 101,'continent1','xyz sq km',1012,'country2','cnt2','pqr sq km',10115,'state5','st5','efg sq km' union all

SELECT 102,'continent2','aaa sq km',1013,'country3','cnt3','pqr sq km',10116,'state6','st6',null union all

SELECT 102,'continent2','aaa sq km',1013,'country3','cnt3','pqr sq km',10117,'state7','st7',null

select count(distinct continent_Name) from @continents

SELECT

(SELECT COUNT(DISTINCT continent_Name) AS CONT_COUNT from @continents C)AS 'continents/count',

t1.continent_id,

t1.continent_Name,

t1.continent_surface_area,

(SELECT COUNT(DISTINCT country_Name) AS COUNTRY_COUNT from @continents CC

WHERE t1.continent_Name = CC.continent_Name) AS 'countries/count',

(SELECT

t2.country_id,

t2.country_Name,

t2.country_code,

t2.country_surface_area,

(SELECT COUNT(DISTINCT CS.State_Name) STATE_COUNT

FROM @continents CS

WHERE t2.country_id = CS.country_id) AS 'states/count',

(SELECT

t3.State_id,

t3.State_Name,

t3.State_code,

t3.State_surface_area

FROM @continents t3

WHERE t3.country_id = t2.country_id

FOR XML PATH('state'),ROOT('states'),TYPE)

FROM @continents t2

WHERE t2.continent_id = t1.continent_id

GROUP BY t2.country_id,

t2.country_Name,

t2.country_code,

t2.country_surface_area

FOR XML PATH('country'),ROOT('countries'),TYPE)

FROM @continents t1

GROUP BY t1.continent_id,

t1.continent_Name,

t1.continent_surface_area

FOR XML PATH('continent'),ROOT('continents'),TYPE;

Results

<continents>

<continent>

<continents>

<count>2</count>

</continents>

<continent_id>101</continent_id>

<continent_Name>continent1</continent_Name>

<continent_surface_area>xyz sq km</continent_surface_area>

<countries>

<count>2</count>

</countries>

<countries>

<country>

<country_id>1011</country_id>

<country_Name>country1</country_Name>

<country_code>cnt1</country_code>

<country_surface_area>abc sq km</country_surface_area>

<states>

<count>3</count>

</states>

<states>

<state>

<State_id>10111</State_id>

<State_Name>state1</State_Name>

<State_code>st1</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

<state>

<State_id>10112</State_id>

<State_Name>state2</State_Name>

<State_code>st2</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

<state>

<State_id>10113</State_id>

<State_Name>state3</State_Name>

<State_code>st3</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

</states>

</country>

<country>

<country_id>1012</country_id>

<country_Name>country2</country_Name>

<country_code>cnt2</country_code>

<country_surface_area>pqr sq km</country_surface_area>

<states>

<count>2</count>

</states>

<states>

<state>

<State_id>10114</State_id>

<State_Name>state4</State_Name>

<State_code>st4</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

<state>

<State_id>10115</State_id>

<State_Name>state5</State_Name>

<State_code>st5</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

</states>

</country>

</countries>

</continent>

<continent>

<continents>

<count>2</count>

</continents>

<continent_id>102</continent_id>

<continent_Name>continent2</continent_Name>

<continent_surface_area>aaa sq km</continent_surface_area>

<countries>

<count>1</count>

</countries>

<countries>

<country>

<country_id>1013</country_id>

<country_Name>country3</country_Name>

<country_code>cnt3</country_code>

<country_surface_area>pqr sq km</country_surface_area>

<states>

<count>2</count>

</states>

<states>

<state>

<State_id>10116</State_id>

<State_Name>state6</State_Name>

<State_code>st6</State_code>

</state>

<state>

<State_id>10117</State_id>

<State_Name>state7</State_Name>

<State_code>st7</State_code>

</state>

</states>

</country>

</countries>

</continent>

</continents>

Jampandu Ten Centuries Points: 1062 More actions · Answer 7

Hi Eirik,

I need to add the count element in between <continents>and <continent> , <countries> and <country>, ....

something like below

<count>2</count>

-----------

<count>2</count>

-----------

<count>1</count>

<state>

-----------

</state>

</states>

</country>

</countries>

</continent>

</continents>

Thanks

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 8

Then we have to do this slightly differently than Mark's solution, this code should be easy to understand

😎

USE tempdb;

GO

SET NOCOUNT ON;

Declare @continents Table

(

id int identity (1,1)

,continent_id int

,continent_Name varchar(100)

,continent_surface_area varchar(100)

,country_id int

,country_Name varchar(100)

,country_code varchar(10)

,country_surface_area varchar(100)

,State_id int

,State_Name varchar(100)

,State_code varchar(10)

,State_surface_area varchar(100)

)

INSERT INTO @continents

SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10111,'state1','st1','efg sq km' union all

SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10112,'state2','st2','efg sq km' union all

SELECT 101,'continent1','xyz sq km',1011,'country1','cnt1','abc sq km',10113,'state3','st3','efg sq km' union all

SELECT 101,'continent1','xyz sq km',1012,'country2','cnt2','pqr sq km',10114,'state4','st4','efg sq km' union all

SELECT 101,'continent1','xyz sq km',1012,'country2','cnt2','pqr sq km',10115,'state5','st5','efg sq km' union all

SELECT 102,'continent2','aaa sq km',1013,'country3','cnt3','pqr sq km',10116,'state6','st6',null union all

SELECT 102,'continent2','aaa sq km',1013,'country3','cnt3','pqr sq km',10117,'state7','st7',null

;WITH CONTINENTS AS

(

SELECT

COUNT(DISTINCT C.continent_id) AS CONTINENT_COUNT

FROM @continents C

)

,CONTINENT AS

(

SELECT DISTINCT

C.continent_id

,C.continent_Name

,C.continent_surface_area

FROM @continents C

)

,COUNTRIES AS

(

SELECT

C.continent_id

,COUNT(C.country_id) AS COUNTRY_COUNT

FROM @continents C

GROUP BY C.continent_id

)

,COUNTRY AS

(

SELECT DISTINCT

C.continent_id

,C.country_id

,C.country_Name

,C.country_code

,C.country_surface_area

FROM @continents C

)

,STATES AS

(

SELECT

C.country_id

,COUNT(C.State_id) AS STATE_COUNT

FROM @continents C

GROUP BY C.country_id

)

,STATE AS

(

SELECT DISTINCT

C.country_id

,C.State_id

,C.State_Name

,C.State_code

,C.State_surface_area

FROM @continents C

)

SELECT

CNS.CONTINENT_COUNT AS 'count'

,(

SELECT

CN.continent_id AS 'continent_id'

,CN.continent_Name AS 'continent_Name'

,CN.continent_surface_area AS 'continent_surface_area'

,(

SELECT

CNTRS.COUNTRY_COUNT AS 'count'

,(

SELECT

CNTRY.country_id

,CNTRY.country_Name

,CNTRY.country_code

,(

SELECT

STS.STATE_COUNT AS 'count'

,(

SELECT

ST.State_id

,ST.State_Name

,ST.State_code

,ST.State_surface_area

FROM STATE ST

WHERE STS.country_id = ST.country_id

FOR XML PATH('state'),TYPE

)

FROM STATES STS

WHERE STS.country_id = CNTRY.country_id

FOR XML PATH('states'),TYPE

)

FROM COUNTRY CNTRY

WHERE CNTRY.continent_id = CNTRS.continent_id

FOR XML PATH('country'),TYPE

)

FROM COUNTRIES CNTRS

WHERE CN.continent_id = CNTRS.continent_id

FOR XML PATH('countries'),TYPE

)

FROM CONTINENT CN

FOR XML PATH('continent'), TYPE

)

FROM CONTINENTS CNS

FOR XML PATH(''), ROOT('continents')

;

Results

<continents>

<count>2</count>

<continent>

<continent_id>101</continent_id>

<continent_Name>continent1</continent_Name>

<continent_surface_area>xyz sq km</continent_surface_area>

<countries>

<count>5</count>

<country>

<country_id>1011</country_id>

<country_Name>country1</country_Name>

<country_code>cnt1</country_code>

<states>

<count>3</count>

<state>

<State_id>10111</State_id>

<State_Name>state1</State_Name>

<State_code>st1</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

<state>

<State_id>10112</State_id>

<State_Name>state2</State_Name>

<State_code>st2</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

<state>

<State_id>10113</State_id>

<State_Name>state3</State_Name>

<State_code>st3</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

</states>

</country>

<country>

<country_id>1012</country_id>

<country_Name>country2</country_Name>

<country_code>cnt2</country_code>

<states>

<count>2</count>

<state>

<State_id>10114</State_id>

<State_Name>state4</State_Name>

<State_code>st4</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

<state>

<State_id>10115</State_id>

<State_Name>state5</State_Name>

<State_code>st5</State_code>

<State_surface_area>efg sq km</State_surface_area>

</state>

</states>

</country>

</countries>

</continent>

<continent>

<continent_id>102</continent_id>

<continent_Name>continent2</continent_Name>

<continent_surface_area>aaa sq km</continent_surface_area>

<countries>

<count>2</count>

<country>

<country_id>1013</country_id>

<country_Name>country3</country_Name>

<country_code>cnt3</country_code>

<states>

<count>2</count>

<state>

<State_id>10116</State_id>

<State_Name>state6</State_Name>

<State_code>st6</State_code>

</state>

<state>

<State_id>10117</State_id>

<State_Name>state7</State_Name>

<State_code>st7</State_code>

</state>

</states>

</country>

</countries>

</continent>

</continents>