XML to T-SQL Table output
-
Tuesday, February 12, 2013 7:07 AM
HI All,
I ahve follwoing XML format,
<Main> <Building01> <BuildingName>Building Name 01</BuildingName> <BuildingColor>Blue</BuildingColor> <Floor01> <name>Floor01</name> <Room1> <Text>Room1</Text> <Size>10</Size> </Room1> <Room2> <Text>Room2</Text> <Size>12</Size> <Table1> <Text>Table1</Text> <Size>5</Size> </Table1> <Table2> <Text>Table2</Text> <Size>4</Size> </Table2> </Room2> </Floor01> <Floor02> <name>Floor02</name> <Hall01> <Text>Hall01</Text> <Size>20</Size> </Hall01> <Hall02> <Text>Hall02</Text> <Size>25</Size> <Table1> <Text>Table1</Text> <Size>7</Size> </Table1> </Hall02> <Hall03> <Text>Hall03</Text> <Size>25</Size> <Sound>Yes</Sound> </Hall03> <Hall04> <Text>Hall04</Text> <Size>30</Size> <From> <Text>From</Text> <value>06/Feb/2013</value> </From> <To> <Text>To</Text> <value>07/Feb/2013</value> </To> <Reason> <Text>School Conference</Text> <value>80</value> </Reason> </Hall04> </Floor02> </Building01> <Building02> <BuildingName>Building Name 02</BuildingName> <BuildingColor>Black</BuildingColor> <Floor01> <name>Floor01</name> <Room1> <Text>Room1</Text> <Size>8</Size> </Room1> </Floor01> </Building02> </Main>I need to get follwoing output as table,
BuildingName BuildingColor Floor Type Size Table Size Sound From Value to Value Reason Value Building Name 01 Blue Floor01 Room1 10 Building Name 01 Blue Floor01 Room2 12 Table1 5 Building Name 01 Blue Floor01 Room2 12 Table2 4 Building Name 01 Blue Floor02 Hall01 20 Building Name 01 Blue Floor02 Hall02 25 Table1 7 Building Name 01 Blue Floor02 Hall03 25 Yes Building Name 01 Blue Floor02 Hall04 30 From 6-Feb-13 To 7-Feb-13School Conference 80 Building Name 02 Black Floor01 Room1 8
if you have any better way to design xml file apart from this then appreciated your comment.
Tharindu Dhaneenja http://databaseusergroup.com
All Replies
-
Tuesday, February 12, 2013 8:32 AMModerator
Hi Tharindu,
If the sample XML data was better tagged it will be easier to fetch data
For example you have Building01 and Building02 named tags for same type of records.
Those 01 and 02 can be stored in XML as an attribute.
And you can use such a tag construction <Building id="01">
-
Tuesday, February 12, 2013 8:54 AM
Hi Eralper,
Thanks for the reply.hope you ask me to change like this,
<Main> <Building id="01" Name="Building Name 01" Color="Blue"> <Floor id="01" Name="Floor 01"> <Room id="01"> <Text>Room 01</Text> <Size>10</Size> </Room> <Room id="02"> <Text>Room 02</Text> <Size>12</Size> <Table id="01"> <Text>Table1</Text> <Size>5</Size> </Table> <Table id="02"> <Text>Table 02</Text> <Size>4</Size> </Table> </Room> </Floor> <Floor id="02" Name="Floor 02"> <Hall id="01"> <Text>Hall01</Text> <Size>20</Size> </Hall> <Hall id="02"> <Text>Hall02</Text> <Size>25</Size> <Table id="01"> <Text>Table 01</Text> <Size>7</Size> </Table> </Hall> <Hall id="03"> <Text>Hall 03</Text> <Size>25</Size> <Sound>Yes</Sound> </Hall> <Hall id="04"> <Text>Hall 04</Text> <Size>30</Size> <From> <Text>From</Text> <value>06/Feb/2013</value> </From> <To> <Text>To</Text> <value>07/Feb/2013</value> </To> <Reason> <Text>School Conference</Text> <value>80</value> </Reason> </Hall> </Floor> </Building> <Building id="02" Name="Building Name 02" Color="Black"> <Floor01> <name>Floor01</name> <Room1> <Text>Room1</Text> <Size>8</Size> </Room1> </Floor01> </Building> </Main>So how can i get output using T-SQL table. i have got stuck this,
declare @xml_data xml='<Main> <Building id="01" Name="Building Name 01" Color="Blue"> <Floor id="01" Name="Floor 01"> <Room id="01"> <Text>Room 01</Text> <Size>10</Size> </Room> <Room id="02"> <Text>Room 02</Text> <Size>12</Size> <Table id="01"> <Text>Table1</Text> <Size>5</Size> </Table> <Table id="02"> <Text>Table 02</Text> <Size>4</Size> </Table> </Room> </Floor> <Floor id="02" Name="Floor 02"> <Hall id="01"> <Text>Hall01</Text> <Size>20</Size> </Hall> <Hall id="02"> <Text>Hall02</Text> <Size>25</Size> <Table id="01"> <Text>Table 01</Text> <Size>7</Size> </Table> </Hall> <Hall id="03"> <Text>Hall 03</Text> <Size>25</Size> <Sound>Yes</Sound> </Hall> <Hall id="04"> <Text>Hall 04</Text> <Size>30</Size> <From> <Text>From</Text> <value>06/Feb/2013</value> </From> <To> <Text>To</Text> <value>07/Feb/2013</value> </To> <Reason> <Text>School Conference</Text> <value>80</value> </Reason> </Hall> </Floor> </Building> <Building id="02" Name="Building Name 02" Color="Black"> <Floor01> <name>Floor01</name> <Room1> <Text>Room1</Text> <Size>8</Size> </Room1> </Floor01> </Building> </Main> ' SELECT Tbl.Col.value('local-name(./.)', 'varchar(max)') [Building], Tbl.Col.value('(./Name)[1]', 'varchar(max)') [BuildingName] FROM @xml_data.nodes('Main/*') Tbl(Col)Tharindu Dhaneenja http://databaseusergroup.com
-
Tuesday, February 12, 2013 9:13 AMModerator
Hello again :)
Now you can change the BuildingName column to use attributes with notation @
Here is your sample updated
SELECT Tbl.Col.value('local-name(./.)', 'varchar(max)') [Building], Tbl.Col.value('(./@Name)[1]', 'varchar(max)') [BuildingName] FROM @xml_data.nodes('Main/*') Tbl(Col) -
Tuesday, February 12, 2013 9:21 AM
Hi Eralper,
The problem was i need follwoing output. not only two columns,
Tharindu Dhaneenja http://databaseusergroup.com
-
Tuesday, February 12, 2013 9:45 AM
You may proceed as follows
declare @xml_data xml='<Main> <Building id="01" Name="Building Name 01" Color="Blue"> <Floor id="01" Name="Floor 01"> <Room id="01"> <Text>Room 01</Text> <Size>10</Size> </Room> <Room id="02"> <Text>Room 02</Text> <Size>12</Size> <Table id="01"> <Text>Table1</Text> <Size>5</Size> </Table> <Table id="02"> <Text>Table 02</Text> <Size>4</Size> </Table> </Room> </Floor> <Floor id="02" Name="Floor 02"> <Hall id="01"> <Text>Hall01</Text> <Size>20</Size> </Hall> <Hall id="02"> <Text>Hall02</Text> <Size>25</Size> <Table id="01"> <Text>Table 01</Text> <Size>7</Size> </Table> </Hall> <Hall id="03"> <Text>Hall 03</Text> <Size>25</Size> <Sound>Yes</Sound> </Hall> <Hall id="04"> <Text>Hall 04</Text> <Size>30</Size> <From> <Text>From</Text> <value>06/Feb/2013</value> </From> <To> <Text>To</Text> <value>07/Feb/2013</value> </To> <Reason> <Text>School Conference</Text> <value>80</value> </Reason> </Hall> </Floor> </Building> <Building id="02" Name="Building Name 02" Color="Black"> <Floor01> <name>Floor01</name> <Room1> <Text>Room1</Text> <Size>8</Size> </Room1> </Floor01> </Building> </Main> ' SELECT Bld.Col.value('local-name(./.)', 'varchar(max)') [Building] ,Bld.Col.value('(./@id)[1]', 'varchar(max)') [BuildingId] ,Bld.Col.value('(./@Name)[1]', 'varchar(max)') [BuildingName] ,Floors.Col.value('local-name(./.)', 'varchar(max)') [Floor] ,Floors.Col.value('(./@id)[1]', 'varchar(max)') [FloorId] ,Floors.Col.value('(./@Name)[1]', 'varchar(max)') [FloorName] ,Rooms.Col.value('local-name(./.)', 'varchar(max)') [RoomType] ,Rooms.Col.value('(./@id)[1]', 'varchar(max)') [RoomId] ,Rooms.Col.value('(./@Name)[1]', 'varchar(max)') [RoomName] FROM @xml_data.nodes('Main/*') Bld(Col) cross apply Bld.Col.nodes('./*') Floors(Col) cross apply Floors.Col.nodes('./*') Rooms(Col)Serg
- Marked As Answer by Tharindu Dhaneenja Tuesday, February 12, 2013 9:49 AM
-
Tuesday, February 12, 2013 9:48 AM
try :
SELECT b.i.value('@id','INT') BuildingID, b.i.value('@Name','VARCHAR(20)') BuildingName, b.i.value('@Color','VARCHAR(20)') BuildingColor, f.i.value('@id','INT') floorID, f.i.value('@Name','VARCHAR(20)') floorName, r.i.value('@id','INT') roomID, r.i.value('./Text[1]','VARCHAR(20)') roomName, r.i.value('./Size[1]','VARCHAR(20)') RoomSize, t.i.value('@id','INT') AS TableID, t.i.value('./Text[1]','VARCHAR(20)') as TableName, t.i.value('./Size[1]','VARCHAR(20)') AS TableSize FROM @xml_data.nodes('/Main/Building') as b(i) CROSS APPLY b.i.nodes('Floor') as f(i) CROSS APPLY f.i.nodes('Room') as r(i) CROSS APPLY r.i.nodes('Table') as t(i)Thanks and regards, Rishabh K
-
Tuesday, February 12, 2013 2:13 PM
A good-looking solution but doesn't include building colour, reason etc. I would do it like this:
DECLARE @xml XML SET @xml = '<Main> <Building id="01" Name="Building Name 01" Color="Blue"> <Floor id="01" Name="Floor 01"> <Room id="01"> <Text>Room 01</Text> <Size>10</Size> </Room> <Room id="02"> <Text>Room 02</Text> <Size>12</Size> <Table id="01"> <Text>Table1</Text> <Size>5</Size> </Table> <Table id="02"> <Text>Table 02</Text> <Size>4</Size> </Table> </Room> </Floor> <Floor id="02" Name="Floor 02"> <Hall id="01"> <Text>Hall01</Text> <Size>20</Size> </Hall> <Hall id="02"> <Text>Hall02</Text> <Size>25</Size> <Table id="01"> <Text>Table 01</Text> <Size>7</Size> </Table> </Hall> <Hall id="03"> <Text>Hall 03</Text> <Size>25</Size> <Sound>Yes</Sound> </Hall> <Hall id="04"> <Text>Hall 04</Text> <Size>30</Size> <From> <Text>From</Text> <value>06/Feb/2013</value> </From> <To> <Text>To</Text> <value>07/Feb/2013</value> </To> <Reason> <Text>School Conference</Text> <value>80</value> </Reason> </Hall> </Floor> </Building> <Building id="02" Name="Building Name 02" Color="Black"> <Floor Name="Floor01" > <Room> <Text>Room1</Text> <Size>8</Size> </Room> </Floor> </Building> </Main>' -- Building Name, Building Colour, Floor, Type, Size, Table, Size, Sound, From, Value, to, Value, Reason, Value SELECT b.c.value('@Name', 'varchar(50)') [Building Name] ,b.c.value('@Color', 'varchar(50)') [Building Color] ,f.c.value('@Name', 'varchar(50)') [Floor] ,r.c.value('local-name(.)', 'varchar(50)') [Type] ,r.c.value('(Size/text())[1]', 'varchar(50)') Size ,t.c.value('(Text/text())[1]', 'varchar(50)') [Table] ,t.c.value('(Size/text())[1]', 'varchar(50)') [Table Size] ,NULL AS [Sound] -- not in source XML ,r.c.value('(From/Text/text())[1]', 'varchar(50)') [From] ,r.c.value('(From/value/text())[1]', 'varchar(50)') [From Value] --!!TODO cleanup date format ,r.c.value('(To/Text/text())[1]', 'varchar(50)') [To] ,r.c.value('(To/value/text())[1]', 'varchar(50)') [To Value] --!!TODO cleanup date format ,r.c.value('(Reason/Text/text())[1]', 'varchar(50)') Reason ,r.c.value('(Reason/value/text())[1]', 'varchar(50)') [Reason Value] FROM @xml.nodes('Main/Building') b(c) CROSS APPLY b.c.nodes('Floor') f(c) CROSS APPLY f.c.nodes('Hall, Room') r(c) OUTER APPLY r.c.nodes('Table') t(c)- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, February 12, 2013 2:26 PM
.png)
