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.