Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Using OPENXML to Shred XML Data – Querychat

The SQL Server OPENXML is a classic function that allows us to query XML data into a tabular format. The process is essentially called “shredding XML.” In this article, we will look at how to use this function to shred XML data.

Many legacy applications use OPENXML to shred XML documents, and there seems to be a lot of developers that still use this function exclusively. Overall, OPENXML is still a topic that’s worth studying.

Syntax

The syntax for the OPENXML is as follows:

OPENXML (idoc, rowpattern, flags) 
[WITH (SchemaDefinition | TableName)]  

The arguments for this command are described in Table 1 below.

Argument Description
idoc This is a handle to the internal representation of the XML document in memory.
rowpattern This defines the XPath pattern used to return nodes from the XML document.
flags This is an optional input parameter and can be one of the values specified in Table 2.
SchemaDefinition | TableName  The SchemaDefinition defines the column name, type, pattern, and optional metadata properties about the XML nodes. For the complete list of metaproperties used in OPENXML, see this link.

If we use the TableName instead of SchemaDefinition, the table must already exist in the database.

Table 1. OPENXML arguments

The OPENXML flag parameter values:

Value Description
0 When the flag 0 is used, the attribute-centric mapping is applied. This is the default value if we don’t pass any value to the OPENXML function.
1 When the flag 1 is used, the attribute-centric mapping is applied.
2 This flag specifies the element-centric mapping.
3 This flag combines flags 1 and 2 to provide the attribute-centric mapping first then followed by the element-centric mapping. 
8 This flag indicates that the consumed data should not be copied to the overflow property.
9 This flag combines flags 1 and 8.
10 This flag combines flags 2 and 8.
11 This flag combines flags 1, 2, and 8.

Table 2. OPENXML flag parameter values

The OPENXML requires the use of system stored procedures, and this makes this function a bit complex for beginners. However, let’s see a simple example below that shreds an XML data and output the results into a tabular format.

T-SQL OPENXML example

Let’s copy and paste the following T-SQL script into a new query editor.

DECLARE @doc VARCHAR(1000), @idoc INT;

SET @doc = '<Contacts>
<Contact Id="1">
<FirstName>Rachel</FirstName>
<LastName>Heard</LastName>
</Contact>
<Contact Id="2">
<FirstName>Julia</FirstName>
<LastName>Evans</LastName>
</Contact>
</Contacts>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

SELECT  *
FROM    OPENXML (@idoc, '/Contacts/Contact', 3)
        WITH (
Id INT,

FirstName VARCHAR(20),
LastName VARCHAR(20)
  )

EXEC sp_xml_removedocument @idoc;

Query 1. OPENXML example query

Notice that in between the calls to sp_xml_preparedocument and sp_xml_removedocument lies the heart of the OPENXML query. 

Result and explanation

Now, let’s execute the script. We will see the following result, which is in a format that looks like a table:

Figure 1. OPENXML example result

Let’s discuss the code we have in Query 1

In the first line, we declared two variables. The variable @doc is used to hold the unprocessed XML data, and the variable @idoc is used to handle the internal representation of the XML data in memory after it’s been processed.

Then, we assigned XML data defined as VARCHAR into the @doc variable. In this case, we have the XML inline in the script. However, we can also read XML from a file, from a table, or use any other source of XML.

Next, we called the sp_xml_preparedocument system stored procedure. It takes the @doc as the input and produces @idoc as an output, which is an INT document handle in memory. The OPENXML uses the document handle returned by this procedure to query the XML document, making OPENXML completely dependent on this procedure. 

After that, we run a SQL query that uses OPENXML to look at the internal representation of the XML document in memory. Notice that the row pattern is set to /Contacts/Contact, and the flag is set to 3, which means that the query will parse all attributes and elements of the /Contacts/Contact nodes.

Finally, the sp_xml_removedocument procedure must be called once we’ve finished querying our XML to release the memory allocated to the previously prepared XML document.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc