SELECT or Query nodes in hierarchial or nested XML
On this post I’ll show how to retrieve information from nested XML.
I’ve shown this with an example with 2 approaches/methods and it’s up to you what to chose based upon their performance. Here we go:
DECLARE @xml XML SET @xml=N'<Root> <orderHRD ID="101"> <custID>501</custID> <orderDTL ID="201"> <prodID>1</prodID> <qty>5</qty> <cost>25.12</cost> </orderDTL> <orderDTL ID="202"> <prodID>2</prodID> <qty>3</qty> <cost>30.00</cost> </orderDTL> </orderHRD> <orderHRD ID="102"> <custID>502</custID> <orderDTL ID="203"> <prodID>11</prodID> <qty>12</qty> <cost>140.78</cost> </orderDTL> </orderHRD> <orderHRD ID="103"> <custID>503</custID> <orderDTL ID="204"> <prodID>6</prodID> <qty>8</qty> <cost>60.35</cost> </orderDTL> <orderDTL ID="205"> <prodID>9</prodID> <qty>2</qty> <cost>10.50</cost> </orderDTL> <orderDTL ID="206"> <prodID>10</prodID> <qty>6</qty> <cost>120.89</cost> </orderDTL> </orderHRD> </Root>'; -- Method #1: Query nested XML nodes by traversing backward and forward. -- Query Cost: 81% select Tab.Col.value('../@ID', 'int') as OrderHDR_ID, Tab.Col.value('../custID[1]', 'int') as Cust_ID, Tab.Col.value('@ID', 'int') as OrderDTL_ID, Tab.Col.value('prodID[1]', 'int') as Prod_ID, Tab.Col.value('qty[1]', 'int') as QTY, Tab.Col.value('cost[1]', 'float') as Cost, Tab.Col.value('count(../orderDTL)', 'int') as Cust_Ord_Count from @xml.nodes('/Root/orderHRD/orderDTL') Tab(Col) -- Method #2: Query nested XML nodes by using CROSS APPLY on appropriate node. -- Query Cost: 19% select Tab.Col.value('@ID', 'int') as OrderHDR_ID, Tab.Col.value('custID[1]', 'int') as Cust_ID, Tab1.Col1.value('@ID', 'int') as OrderDTL_ID, Tab1.Col1.value('prodID[1]', 'int') as Prod_ID, Tab1.Col1.value('qty[1]', 'int') as QTY, Tab1.Col1.value('cost[1]', 'float') as Cost, Tab.Col.value('count(./orderDTL)', 'int') as Cust_Ord_Count from @xml.nodes('/Root/orderHRD') as Tab(Col) cross apply Tab.Col.nodes('orderDTL') as Tab1(Col1) GO
Output:- OrderHDR_ID Cust_ID OrderDTL_ID Prod_ID QTY Cost 101 501 201 1 5 25.12 101 501 202 2 3 30 102 502 203 11 12 140.78 103 503 204 6 8 60.35 103 503 205 9 2 10.5 103 503 206 10 6 120.89
Method #2 is much more effecient than first one. As 1st method uses forward-backward traversal approach. The 2nd method uses APPLY operator which gets Nodes information from a specific parent.
This topic was also discussed on MSDN TSQL’s forum few weeks back, link.
>> Check & Subscribe my [YouTube videos] on SQL Server.
Categories: XML
APPLY, APPLY Operator, APPLY XML, CROSS APPLY, XML, XML Nodes, XML Query Optimization, XML Query Performance
Helped me understand several aspects of XML and cross apply. Very powerful example.
Very nice article, helped me a lot. I spend almost a day to get this kind of result and finally got success after reading your article. Thanks a lot.
Very nice article, helped me a lot
hi,
Thank you for your post.. it helped me a lot.
please note the second query should be like that first column from Tab.Col.value to fetch OrderHDR_ID not OrderDTL_ID also Third Column should be Tab1.Col1.Value to fetch OrderDTL_ID not OrderHDR_ID as following
select
Tab.Col.value(‘@ID’, ‘int’) as OrderHDR_ID,
Tab.Col.value(‘custID[1]’, ‘int’) as Cust_ID,
Tab1.Col1.value(‘@ID’, ‘int’) as OrderDTL_ID,
Tab1.Col1.value(‘prodID[1]’, ‘int’) as Prod_ID,
Tab1.Col1.value(‘qty[1]’, ‘int’) as QTY,
Tab1.Col1.value(‘cost[1]’, ‘float’) as Cost,
Tab.Col.value(‘count(./orderDTL)’, ‘int’) as Cust_Ord_Count
from @xml.nodes(‘/Root/orderHRD’) as Tab(Col)
cross apply Tab.Col.nodes(‘orderDTL’) as Tab1(Col1)
GO
Thanks @e.person, for pointing it out.
Didn’t observe that the 2 columns were swapped, I’ve corrected it now.
was really helpful
Thanks Dani 🙂
Thank you so much. Very clear and helpful.
Thanks. Clear, concise and helpful.
Hello,
first thanks for the good article.
I try to query through a deadlock graph xml to gather information out of it.
I have problems to access the procname:
/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/executionStack/frame/@procname[1]
I used your example and go most of the things extracted properly but here I stuck to access this element in
DEADLOCK_GRAPH
xxx
xx
there are 4 process ids where each consits of the attributes ..
— SQL Code —
DECLARE @xml XML = (select [DeadlockGraph].query(‘.’)
from SQLDeadlockEvents
where EventRowID = 33)
SELECT
Tab.Col.value(‘@id’,’nvarchar(20)’) AS ID,
Tab.Col.value(‘@currentdb’,’nvarchar(20)’) AS DatabaseID,
Tab.Col.value(‘@loginname’,’nvarchar(20)’) AS Login,
Tab.Col.value(‘/executionStack/frame/@procname[1]’,’nvarchar(20)’) AS ProcName
FROM @xml.nodes(‘/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process’) Tab(Col)
— Output —
Msg 2389, Level 16, State 1, Line 22
XQuery [value()]: ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *’
How can I access now the procname ..
Thanks for your advice.
Kind regards
Awesome. A perfect solution for nested xml to SQL, I really appreciate this article.
Hi Manoj
It is indeed very good article. I have very similar situation as this article… have XML below and return data of node “attachDocument”… I wrote script to return 7 rows (7 nodes of “atttachDocument” under consignment node) but should be 8 (one underroot node “declaratio”) that I am struggling to return it.. Need help with it.
Script…
;WITH XMLNAMESPACES (DEFAULT ‘http://xyzs.govt.nz/jbms/cmm/decl/v2’)
SELECT response.value(‘(category/text())[1]’,’varchar(50)’) as Category,
response.value(‘(mimeCode/text())[1]’,’varchar(50)’) as MimeCode,
response.value(‘(URI/text())[1]’,’varchar(50)’) as URI,
response.value(‘(filename/text())[1]’,’varchar(50)’) as [FileName],
response.value(‘(contentSize/text())[1]’,’varchar(50)’) as ContentSize,
response.value(‘(sequence/text())[1]’,’varchar(100)’) as Sequence
FROM @sqlxml.nodes(‘/declaration/consignment/attachDocument’) AS T(response);
As your 8th atttachDocument element is directly under declaration, so you just need to add it as a separate UNION:
UNION
SELECT
response.value(‘(category/text())[1]’,’varchar(50)’) as Category,
response.value(‘(mimeCode/text())[1]’,’varchar(50)’) as MimeCode,
response.value(‘(URI/text())[1]’,’varchar(50)’) as URI,
response.value(‘(filename/text())[1]’,’varchar(50)’) as [FileName],
response.value(‘(contentSize/text())[1]’,’varchar(50)’) as ContentSize,
response.value(‘(sequence/text())[1]’,’varchar(100)’) as Sequence
FROM @sqlxml.nodes(‘/declaration/attachDocument’) AS T(response);
Not sure how to add XML in correct format. is any way I can send you?
can you email the XML file to me on my email ID: manub22 (at) gmail?
I have sent an email on 7 May on yur gmail account subject “Query nested XML”
Thanks for reply Manoj… I have asked for help on another Query of XML and sent via email…