OPENXML – Basic Examples

Introduction
OPENXML is used to produce a rowset over an XML document. It isn’t something I’ve ever used and it took a while to understand the use of flags that it can use. The following article contains several examples, using the various flag settings as well as an example of the metaproperty attributes.

Background
OPENXML has three parameters and a WITH clause. It isn’t able to look at an XML document directly, so requires the stored procedure ‘sp_xml_preparedocument’ to process the XML and return a handle that OPENXML can then use to access the parsed XML data. With this handle OPENXML can then access the elements or the attributes of the XML and can also control which data it additionally copies to the overflow property ‘@mp:xmltext’.

What is an element or an attribute?
In the example code below I use

 <Customer custid="3001">

In this snippet ‘custid’ is an attribute of ‘Customer’. In

 <companyname>Customer Test2</companyname>

‘companyname’ is an element.

OPENXML flag
OPENXML can extract data from the associated XML document (via the handle parameter) based upon the attribute names (a flag value of 1), the element names (a flag value of 2) or both (a flag value of 3).
In addition, when processing the XML for a row, the XML it uses is output to a property ‘@mp:xmltext’. If the flag is set to 8 then only the XML that it did not process for the row is written to this variable, otherwise all row data is written to it.

These values can be combined:
Flag value of 9 – attributes extracted and unprocessed data to the overflow property.
Flag value of 10 – elements extracted and unprocessed data to the overflow property.
Flag value of 11 – element and attributes extraced, as well as unprocessed data to the overflow property.

Test Execution

In the first example I have code that is supposed to extract ‘custid’, ‘companyname’ and ‘Order’. These names are case-sensitive, which is why ‘Order’ begins with a capital letter.
Because I have set the flag to 1, it will match these names against the attributes with NULL where it doesn’t find that attribute name.

listing 1: OPENXML with flag = 1

DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example
SET @Xml = N'
<CustomerOrders>
  <Customer custid="3001">
    <companyname>Customer Test1</companyname>
    <Order orderid="112244">
      <orderdate>2017-12-02T00:00:00</orderdate>
    </Order>
  </Customer>
  <Customer custid="3002">
    <companyname>Customer Test2</companyname>
    <Order orderid="113367">
      <orderdate>2017-12-12T00:00:00</orderdate>
    </Order>
  </Customer>
</CustomerOrders>';

EXEC sys.sp_xml_preparedocument @Handle OUTPUT , @Xml; --Prepare a parsed document 

SELECT *
FROM
	   OPENXML(@Handle, '/CustomerOrders/Customer', 1)
		   WITH (	custid NVARCHAR(10) ,
				companyname NVARCHAR(40) ,
				[Order] NVARCHAR(20)
				);

EXEC sys.sp_xml_removedocument @Handle; --Remove the handle
GO

The results from flag = 1

The code tries to match ‘custid’, ‘companyname’ and ‘Order’ against the attributes and only ‘custid’ is an attribute.

listing 2: OPENXML with flag = 2

DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example
SET @Xml = N'
<CustomerOrders>
  <Customer custid="3001">
    <companyname>Customer Test1</companyname>
    <Order orderid="112244">
      <orderdate>2017-12-02T00:00:00</orderdate>
    </Order>
  </Customer>
  <Customer custid="3002">
    <companyname>Customer Test2</companyname>
    <Order orderid="113367">
      <orderdate>2017-12-12T00:00:00</orderdate>
    </Order>
  </Customer>
</CustomerOrders>';

EXEC sys.sp_xml_preparedocument @Handle OUTPUT ,@Xml; --Prepare a parsed document 

SELECT *
FROM
	   OPENXML(@Handle, '/CustomerOrders/Customer', 2)
		   WITH (	custid NVARCHAR(10) ,
				companyname NVARCHAR(40) ,
				[Order] NVARCHAR(20)
				);

EXEC sys.sp_xml_removedocument @Handle; --Remove the handle
GO

The results from flag = 2

In listing 2, a flag set to 2 means that it will now match on element names, so the results are reversed as ‘orderid’ is an attribute.

listing 3: OPENXML with flag = 3

DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example
SET @Xml = N'
<CustomerOrders>
  <Customer custid="3001">
    <companyname>Customer Test1</companyname>
    <Order orderid="112244">
      <orderdate>2017-12-02T00:00:00</orderdate>
    </Order>
  </Customer>
  <Customer custid="3002">
    <companyname>Customer Test2</companyname>
    <Order orderid="113367">
      <orderdate>2017-12-12T00:00:00</orderdate>
    </Order>
  </Customer>
</CustomerOrders>';

EXEC sys.sp_xml_preparedocument @Handle OUTPUT ,@Xml; --Prepare a parsed document 

SELECT *
FROM
	   OPENXML(@Handle, '/CustomerOrders/Customer', 3)
		   WITH (	custid NVARCHAR(10) ,
				companyname NVARCHAR(40) ,
				[Order] NVARCHAR(20)
				);

EXEC sys.sp_xml_removedocument @Handle; --Remove the handle
GO

The results from flag = 3

In listing 3, a flag set to 3 means that it will now match on element names and attribute names, so all of the columns show data.

Metaproprties

listing 4: OPENXML with flag = 3 and overflow data

DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example
SET @Xml = N'
<CustomerOrders>
  <Customer custid="3001">
    <companyname>Customer Test1</companyname>
    <Order orderid="112244">
      <orderdate>2017-12-02T00:00:00</orderdate>
    </Order>
  </Customer>
  <Customer custid="3002">
    <companyname>Customer Test2</companyname>
    <Order orderid="113367">
      <orderdate>2017-12-12T00:00:00</orderdate>
    </Order>
  </Customer>
</CustomerOrders>';

EXEC sys.sp_xml_preparedocument @Handle OUTPUT ,@Xml; --Prepare a parsed document 

SELECT *
FROM
	   OPENXML(@Handle, '/CustomerOrders/Customer', 3)
		   WITH (	custid NVARCHAR(10) ,
				companyname NVARCHAR(40) ,
				[Order] NVARCHAR(20),
				xmlrowdata NVARCHAR(512) '@mp:xmltext'
				);

EXEC sys.sp_xml_removedocument @Handle; --Remove the handle
GO

The results from flag = 3 with overflow data

The results from listing 4 show the entire row data within the XML. If we change the flag to 11 the XML row data will not include data that was extracted for the columns ‘custid’, ‘companyname’ and ‘Order’.

listing 5: OPENXML with flag = 11 and overflow data

DECLARE @Handle AS INT; -- The handle of the XML data, passed to sp_xml_preparedocument
DECLARE @Xml AS NVARCHAR(1000); -- The XML document for this example
SET @Xml = N'
<CustomerOrders>
  <Customer custid="3001">
    <companyname>Customer Test1</companyname>
    <Order orderid="112244">
      <orderdate>2017-12-02T00:00:00</orderdate>
    </Order>
  </Customer>
  <Customer custid="3002">
    <companyname>Customer Test2</companyname>
    <Order orderid="113367">
      <orderdate>2017-12-12T00:00:00</orderdate>
    </Order>
  </Customer>
</CustomerOrders>';

EXEC sys.sp_xml_preparedocument @Handle OUTPUT ,@Xml; --Prepare a parsed document 

SELECT *
FROM
	   OPENXML(@Handle, '/CustomerOrders/Customer', 11)
		   WITH (	custid NVARCHAR(10) ,
				companyname NVARCHAR(40) ,
				[Order] NVARCHAR(20),
				xmlrowdata NVARCHAR(512) '@mp:xmltext'
				);

EXEC sys.sp_xml_removedocument @Handle; --Remove the handle
GO

The results from flag = 11 with overflow data

Other metaproperties exist to provide additional information. Details of these are documented here.

References
Microsoft Docs – Examples: Using OPENXML
ExtremeExperts – OPEN XML – Inside story
Microsoft Docs – Specifying Metaproperties in OPENXML
Microsoft Docs -OPENXML (Transact SQL)
Microsoft Docs – sp_xml_preparedocument (Transact SQL)

One thought on “OPENXML – Basic Examples”

Pingback: SQL Review - Roofer Solution | Ultimate Roofing Software Application

Notes on SQL

Random articles from a puzzled DBA