13 | July | 2011 | SQL with Manoj

SELECT or Query nodes in hierarchial or nested XML

July 13, 2011 23 comments

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 Tags: APPLY, APPLY Operator, APPLY XML, CROSS APPLY, XML, XML Nodes, XML Query Optimization, XML Query Performance

Query XML string in tabular format – SQL Server

July 13, 2011 3 comments

My previous post on XML discusses how to simply SELECT records from an XML string.

On this post I’m giving few more examples on how to SELECT records from an XML string. Here are some more examples to retrieve data in a relational tabular format by querying an XML string:

DECLARE @xml XML

SET @xml=N'<Root>
    <stud id="1">
		<fname>Manoj</fname>
		<lname>Pandey</lname>
		<class>10</class>
		<marks>80.5</marks>
	</stud>
    <stud id="2">
		<fname>Saurabh</fname>
		<lname>Sharma</lname>
		<class>11</class>
		<marks>82.7</marks>
	</stud>
    <stud id="3">
		<fname>Kanchan</fname>
		<lname>Pandey</lname>
		<class>10</class>
		<marks>90.5</marks>
	</stud>
    <stud id="4">
		<fname>Rajesh</fname>
		<lname>Shah</lname>
		<class>11</class>
		<marks>70.3</marks>
	</stud>
    <stud id="5">
		<fname>Kunal</fname>
		<lname>Joshi</lname>
		<class>12</class>
		<marks>64.7</marks>
	</stud>
</Root>';

-- Select all records in tabular format from above XML string:
SELECT
Tab.Col.value('@id','int') AS ID,
Tab.Col.value('fname[1]','nvarchar(20)') AS FirstName,
Tab.Col.value('lname[1]','nvarchar(20)') AS LastName,
Tab.Col.value('class[1]','int') AS class,
Tab.Col.value('marks[1]','float') AS Marks
FROM @xml.nodes('/Root/stud') Tab(Col)

Output:-
ID FirstName LastName Class Marks
1  Manoj     Pandey   10    80.5
2  Saurabh   Sharma   11    82.7
3  Kanchan   Pandey   10    90.5
4  Rajesh    Shah     11    70.3
5  Kunal     Joshi    12    64.7

-- Select records as in Key-Value pair format:
SELECT
Tab.Col.value('../@id', 'int') AS RowID,
Tab.Col.value('local-name(.)', 'nvarchar(max)') AS ColName,
Tab.Col.value('text()[1]', 'nvarchar(max)') AS ColValue
FROM @xml.nodes('/*/*/*') AS Tab(Col)
GO

Output:-
RowID ColName ColValue
1     fname   Manoj
1     lname   Pandey
1     class   10
1     marks   80.5
2     fname   Saurabh
2     lname   Sharma
2     class   11
2     marks   82.7
3     fname   Kanchan
3     lname   Pandey
3     class   10
3     marks   90.5
4     fname   Rajesh
4     lname   Shah
4     class   11
4     marks   70.3
5     fname   Kunal
5     lname   Joshi
5     class   12
5     marks   64.7

Categories: XML Tags: Query XML, XML SQL