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.
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.
|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:
|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.