Systems Engineering and RDBMS

Reading from a XML Data Type Column in SQL Server

Posted by decipherinfosys on November 23, 2011

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.

About these ads

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

 
Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: