Sql Server: OPENXML (sample T-SQL querying techniques using OPENXML)
- Posted by Sqltimes
- On April 9, 2014
- 0 Comments
Sql Server, starting from 2005, has extensive methods to parse, slice & dice XML files. Before that, for DBA’s, XML is not a favorite topic — especially from T-SQL point of view. Application languages like C#, ASP.Net and Java have many methods and programmers usually prefer to handle XML in application code rather than T-SQL. But it all changed since Sql Server 2005 and got much better in Sql Server 2008, 2008 R2 and Sql Server 2012.
Today, we’ll look at OPENXML and how it allows us to convert XML document into a table — then you can query it as you need.
The diagram below is from MSDN, that shows conceptually how XML document is parsed and converted into a table like representation.
From MSDN – OPENXML parses XML file
Let us take an example to demonstrate some of the querying nuances:
|
1
2
3
4
5
6
7
8
9
10
11
|
<root> <Assigned ID="1"> <Store StoreID="14"/> <Store StoreID="15"/> <Store StoreID="17" Name="Atlanta"/> </Assigned> <Assigned ID="2"> <Store StoreID="24" Name="Chicago"/> <Store StoreID="23" Name="Boston"/> </Assigned></root> |
We can query any data values from the above XML as needed. For example, let’s retrieve
- ID from Assigned
- StoreID from Store
- Name from Store
To do this, we
- First read the XML file into a variable
- Use ‘sp_xml_preparedocument’ create XML DOM
- Pass it to OPENXML
- Specify what values you want to read
- Voila !!
Sidebar: ‘sp_xml_preparedocument’ parses XML document into a document object model (DOM) tree representation for easy consumption.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
<pre>DECLARE @xml XML , @docid INT---- sample xml document--SET @xml = N'<root><Assigned ID="1"><Store StoreID="14"/><Store StoreID="15"/><Store StoreID="17" Name="Atlanta"/></Assigned><Assigned ID="2"><Store StoreID="24" Name="Chicago"/><Store StoreID="23" Name="Boston"/></Assigned></root>'---- Prepare the XML input so read from it can be much faster.--EXEC sp_xml_preparedocument @docid OUTPUT, @xml;---- Query XML file to retrieve selected values--SELECT *FROM OPENXML (@docid, '/root/Assigned/Store') -- lowest parsing locationWITH ( AssignedID INT '../@ID' -- go a level above and retrieve @ID from Assigned , StoreID INT '@StoreID' -- value from current parse location , Name VARCHAR(20) '@Name' -- value from current parse location )---- remove XML document from memory--EXEC sp_xml_removedocument @docidGO |
Result:
OPENXML_Results
Sidebar: ‘sp_xml_removedocument‘ removes the document pointer from memory; A good practice to follow.
Hope this helps,
0 Comments