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:

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.

1. 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

2. 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

Leave a Reply Cancel reply