Specify metaproperties in OPENXML

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Metaproperty attributes in an XML document are attributes that describe the properties of an XML item, such as element, attribute, or any other DOM node. These attributes don't physically exist in the XML document text. However, OPENXML provides these metaproperties for all the XML items. These metaproperties allow you to extract information, such as local positioning and namespace information, of XML nodes. This information provides you with more details than are apparent in the textual representation.

You can map these metaproperties to the rowset columns in an OPENXML statement by using the ColPattern parameter. The columns will contain the values of the metaproperties to which they're mapped. For more information about the syntax of OPENXML, see OPENXML (Transact-SQL).

To access the metaproperty attributes, a namespace that is specific to SQL Server is provided. This namespace, urn:schemas-microsoft-com:xml-metaprop allows the user to access the metaproperty attributes. If the result of an OPENXML query is returned in an edge table format, the edge table contains one column for each metaproperty attribute, except the xmltext metaproperty.

Some of the metaproperty attributes are used for processing purposes. For example, the xmltext metaproperty attribute is used for overflow handling. Overflow handling refers to the unconsumed, unprocessed data in the document. One of the columns in the rowset that is generated by OPENXML can be identified as the overflow column. You do this by mapping it to the xmltext metaproperty by using the ColPattern parameter. The column then receives the overflow data. The flags parameter determines whether the column contains everything or only the unconsumed data.

The following table lists the metaproperty attributes that each parsed XML element possesses. These metaproperty attributes can be accessed by using the namespace urn:schemas-microsoft-com:xml-metaprop. Any value that the user sets directly in the XML document by using these metaproperties is ignored.

This table shows the additional parent properties that are provided and which allow you to retrieve information about the hierarchy.

Examples

The following examples illustrate how OPENXML is used to create different rowset views.

A. Map the OPENXML rowset columns to the metaproperties

This example uses OPENXML to create a rowset view of the sample XML document. Specifically, it shows how the various metaproperty attributes can be mapped to rowset columns in an OPENXML statement by using the ColPattern parameter.

The OPENXML statement illustrates the following:

• The id column is mapped to the @mp:id metaproperty attribute and indicates that the column contains the system-generated unique XML ID of the element.

• The parent column is mapped to @mp:parentid and indicates that the column contains the XML ID of the parent of the element.

• The parentLocalName column is mapped to @mp:parentlocalname and indicates that the column contains the local name of the parent.

The SELECT statement then returns the rowset that is provided by OPENXML:

DECLARE @idoc int;
DECLARE @doc nvarchar(1000);
-- Sample XML document
SET @doc = N'<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied</Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue white red">
          <Urgency>Important</Urgency>
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 9)
      WITH (id int '@mp:id',
            oid char(5),
            date datetime,
            amount real,
            parentIDNo int '@mp:parentid',
            parentLocalName varchar(40) '@mp:parentlocalname');
EXEC sp_xml_removedocument @idoc;

This is the result:

id   oid         date                amount    parentIDNo  parentLocalName
--- ------- ---------------------- ---------- ------------ ---------------
6    O1    1996-01-20 00:00:00.000     3.5         2        Customer
10   O2    1997-04-30 00:00:00.000     13.4        2        Customer
19   O3    1999-07-14 00:00:00.000     100.0       15       Customer
25   O4    1996-01-20 00:00:00.000     10000.0     15       Customer

B. Retrieve the whole XML document

In this example, OPENXML is used to create a one-column rowset view of the sample XML document. This column, Col1, is mapped to the xmltext metaproperty and becomes an overflow column. As a result, the column receives the unconsumed data. In this case, it's the whole document.

The SELECT statement then returns the complete rowset.

DECLARE @idoc int;
DECLARE @doc nvarchar(1000);
SET @doc = N'<?xml version="1.0"?>
<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
             satisfied</Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
             white red">
     <MyTag>Testing to see if all the subelements are returned</MyTag>
          <Urgency>Important</Urgency>
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/')
   WITH (Col1 ntext '@mp:xmltext')

To retrieve the whole document without the XML declaration, the query can be specified as shown in the following:

SELECT *
FROM OPENXML (@idoc, '/root')
   WITH (Col1 ntext '@mp:xmltext')
EXEC sp_xml_removedocument @idoc;

The query returns the root element that has the name root and the data that is contained by the root element

C. Specifying the xmltext metaproperty to retrieve the unconsumed data in a column

This example uses OPENXML to create a rowset view of the sample XML document. The example shows how to retrieve unconsumed XML data by mapping the xmltext metaproperty attribute to a rowset column in OPENXML.

The comment column is identified as the overflow column by mapping it to the @mp:xmltext metaproperty. The flags parameter is set to 9 (XML_ATTRIBUTE and XML_NOCOPY). This indicates attribute-centric mapping and indicates that only the unconsumed data should be copied to the overflow column.

The SELECT statement then returns the rowset provided by OPENXML.

In this example, the @mp:parentlocalname metaproperty is set for a column, ParentLocalName, in the rowset generated by OPENXML. As a result, this column contains the local name of the parent element.

Two additional columns are specified in the rowset, parent and comment. The parent column is mapped to @mp:parentid and indicates that the column contains the XML ID of the parent element of the element. The comment column is identified as the overflow column by mapping it to the @mp:xmltext metaproperty.

DECLARE @idoc int;
DECLARE @doc nvarchar(1000);
-- sample XML document
SET @doc = N'<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied</Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue white red">
          <Urgency>Important</Urgency>
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>
';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 9)
      WITH (oid char(5),
            date datetime,
            comment ntext '@mp:xmltext');
EXEC sp_xml_removedocument @idoc;

This is the result. Because the oid columns and date columns are already consumed, they don't appear in the overflow column.

oid   date                        comment
----- --------------------------- ----------------------------------------
O1    1996-01-20 00:00:00.000     <Order amount="3.5"/>
O2    1997-04-30 00:00:00.000     <Order amount="13.4">Customer was very
                                   satisfied</Order>
O3    1999-07-14 00:00:00.000     <Order amount="100" note="Wrap it blue
                                   white red"><Urgency>
                                   Important</Urgency></Order>
O4    1996-01-20 00:00:00.000     <Order amount="10000"/>

See also

• OPENXML (Transact-SQL)
• OPENXML (SQL Server)