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.
 


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

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

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

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

    Very nice article, helped me a lot

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

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

    was really helpful

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

    Thank you so much. Very clear and helpful.

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

    Thanks. Clear, concise and helpful.

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

  9. Krithiga
    April 30, 2019 at 4:36 pm

    Awesome. A perfect solution for nested xml to SQL, I really appreciate this article.

  10. Swarn Singh
    May 7, 2021 at 1:14 pm

    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);

    • May 10, 2021 at 3:17 pm

      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);

  11. Swarn Singh
    May 7, 2021 at 1:20 pm

    Not sure how to add XML in correct format. is any way I can send you?

    • May 7, 2021 at 6:21 pm

      can you email the XML file to me on my email ID: manub22 (at) gmail?

  12. SQLHoncho
    May 10, 2021 at 9:15 am

    I have sent an email on 7 May on yur gmail account subject “Query nested XML”

  13. SQLHoncho
    May 11, 2021 at 9:52 am

    Thanks for reply Manoj… I have asked for help on another Query of XML and sent via email…

  1. September 22, 2011 at 6:36 am
  2. January 3, 2012 at 9:30 am
  3. January 9, 2014 at 11:20 pm
  4. October 19, 2015 at 5:06 pm
  5. October 23, 2015 at 3:55 pm
  6. April 24, 2023 at 12:59 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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: