Advertisements
Home > XML > SELECT or Query nodes in hierarchial or nested XML

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.
 


Advertisements
  1. Michael
    February 16, 2016 at 9:20 pm

    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

  2. Dave
    February 5, 2016 at 10:36 pm

    Thanks. Clear, concise and helpful.

  3. Dave
    February 5, 2016 at 10:34 pm

    Thank you so much. Very clear and helpful.

  4. Dani
    June 19, 2015 at 3:44 pm

    was really helpful

  5. e.person
    May 26, 2015 at 8:52 pm

    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

    • May 26, 2015 at 9:08 pm

      Thanks @e.person, for pointing it out.

      Didn’t observe that the 2 columns were swapped, I’ve corrected it now.

  6. Neelam
    August 7, 2014 at 7:29 pm

    Very nice article, helped me a lot

  7. spider
    March 6, 2014 at 6:23 pm

    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.

  8. J.Farr
    November 25, 2013 at 3:07 am

    Helped me understand several aspects of XML and cross apply. Very powerful example.

  1. October 23, 2015 at 3:55 pm
  2. October 19, 2015 at 5:06 pm
  3. January 9, 2014 at 11:20 pm
  4. January 3, 2012 at 9:30 am
  5. September 22, 2011 at 6:36 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: