We have covered XML capabilities in SQL Server in several posts before – you can read those here. Yesterday, while helping a client, the developer needed to read from an XML data type column and the way the code was written was to iterate over the records, fetch one record at a time, assign it to a variable and then using the nodes() method, extract out the data and their hierarchies into a flat row/column structure as a data set. This approach is not very scalable on large data sets and performance becomes an issue.
One can use the nodes() method and CROSS APPLY/OUTER APPLY to do this in a SET based fashion using a single SQL statement. We had demonstrated this before in some of our Report Server queries as well. Here is an example of how to do that:
We will first create a demo table:
IF EXISTS (SELECT 1 FROM SYS.objects WHERE TYPE = 'U' AND NAME = 'AP_DEMO')
BEGIN
DROP TABLE AP_DEMO
END
GO
CREATE TABLE dbo.AP_DEMO
(
TRANSACTION_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Xml_Data XML NOT NULL
)
GO
And now insert into this table 2 records – note that the XML that is being inserted has a three layer hierarchy – One Transaction can have 1-N Services and 1 Service can have 1-N Responses. Also note that we are using the row value constructor feature of SQL Server 2008 to do these inserts.
INSERT INTO AP_DEMO (XML_DATA)
VALUES
(
‘<?xml version=”1.0″ ?>
<Root>
<Transaction>
<Tattrib1>Test1</Tattrib1>
<Tattrib2>100</Tattrib2>
<Service>
<Id>1</Id>
<Name>Service1</Name>
<Response>
<Key>KeyTest1</Key>
<Value>Value1</Value>
<Point>10</Point>
</Response>
</Service>
</Transaction>
<Transaction>
<Tattrib1>Test2</Tattrib1>
<Tattrib2>53</Tattrib2>
<Service>
<Id>100</Id>
<Name>Service2</Name>
<Response>
<Key>KeyTest2</Key>
<Value>Value2</Value>
<Point>90</Point>
</Response>
</Service>
</Transaction>
</Root>’)
,
(‘<?xml version=”1.0″ ?>
<Root>
<Transaction>
<Tattrib1>Test3</Tattrib1>
<Tattrib2>1000</Tattrib2>
<Service>
<Id>9</Id>
<Name>Service20</Name>
<Response>
<Key>KeyTest3</Key>
<Value>Value3</Value>
<Point>99</Point>
</Response>
</Service>
</Transaction>
<Transaction>
<Tattrib1>Test4</Tattrib1>
<Tattrib2>999</Tattrib2>
<Service>
<Id>87</Id>
<Name>Service30</Name>
<Response>
<Key>KeyTest4</Key>
<Value>Value4</Value>
<Point>97</Point>
</Response>
</Service>
</Transaction>
</Root>’)
And now, we can extract this data using the nodes() method and applying CROSS APPLY at it to extract out each hierarchy:
SELECT
svc.value('(Tattrib1/text())[1]', 'varchar(100)') as Txn_Attribute_1
, svc.value('(Tattrib2/text())[1]', 'varchar(100)') as Txn_Attribute_2
, rsp.value('(Id/text())[1]', 'int') as Service_Id
, rsp.value('(Name/text())[1]', 'nvarchar(100)') as [Service_Name]
, val.value('(Key/text())[1]', 'nvarchar(100)') as Response_Key
, val.value('(Value/text())[1]', 'nvarchar(100)') as TSR_Value
, val.value('(Point/text())[1]', 'int') as TSR_Point
FROM
AP_DEMO
CROSS APPLY Xml_Data.nodes('//Transaction') AS Txn(svc)
CROSS APPLY svc.nodes('Service') AS svc(rsp)
CROSS APPLY rsp.nodes('Response') as rsp(val)
GO
This will give the following output:
Txn_Attribute_1 Txn_Attribute_2 Service_Id Service_Name Response_Key TSR_Value TSR_Point
Test1 100 1 Service1 KeyTest1 Value1 10
Test2 53 100 Service2 KeyTest2 Value2 90
Test3 1000 9 Service20 KeyTest3 Value3 99
Test4 999 87 Service30 KeyTest4 Value4 97
As you can see from above, one can easily extract out the data from a XML data type column in a table by using a single SQL statement. Of course, one should ensure that one has proper filter conditions in the WHERE clause on properly indexed columns and is also making use of XML indexes in order to ensure a good execution plan for the SQL.
Hope this small code snippet helps in your work. Wish all of our readers in the US a very Happy Thanksgiving.
