Specifying Metaproperties in OPENXML

XML and Internet Support

XML and Internet Support

Specifying Metaproperties in OPENXML

Metaproperty attributes in an XML document are attributes that describe the properties of an XML item (element, attribute, or any other DOM node). These attributes do not 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, which provide more details than is visible in the textual representation.

You can map these metaproperties to the rowset columns in an OPENXML statement using the ColPattern parameter. The columns will contain the values of the metaproperties to which they are mapped. For more information about the syntax of OPENXML, see OPENXML.

To access the metaproperty attributes, a namespace specific to Microsoft® SQL Server™ 2000 (urn:schemas-microsoft-com:xml-metaprop) is provided that 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 for the xmltext metaproperty).

Some of the metaproperty attributes are used for processing purposes. For example, 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 generated by OPENXML can be identified as overflow column by mapping it to xmltext metaproperty using the ColPattern parameter. The column then receives the overflow data (the flags parameter determines whether the column contains only the unconsumed data or everything).

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

Note  You cannot reference these metaproperties in any XPath navigation.

Metaproperty attribute Description
@mp:id Provides system-generated, document-wide identifier of the DOM node (element, attribute, and so on). This ID is guaranteed to refer to the same XML node as long as the document is not reparsed.

An XML ID of 0 indicates that the element is a root element. Its parent XML ID is NULL.

@mp:localname Stores the local part of the name of the node. It is used with prefix and namespace URI (Uniform Resource Identifier) to name element or attribute nodes.
@mp:namespaceuri Provides the namespace URI of the current element. If the value of this attribute is NULL, no namespace is present
@mp:prefix Stores the namespace prefix of the current element name.

If no prefix is present (NULL) and a URI is given, indicates that the specified namespace is the default namespace. If no URI is given, no namespace is attached.

@mp:prev Stores the previous sibling relative to a node, thereby, providing information about the ordering of elements in the document.

@mp:prev contains the XML ID of the previous sibling that has the same parent element. If an element is at the beginning of the sibling list, @mp:prev is NULL.

@mp:xmltext This metaproperty is used for processing purposes. Is the textual serialization of the element and its attributes and subelements as used in the overflow handling of OPENXML.

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

Parent metaproperty attribute Description
@mp:parentid Corresponds to ../@mp:id
@mp:parentlocalname Corresponds to ../@mp:localname
@mp:parentnamespacerui Corresponds to ../@mp:namespaceuri
@mp:parentprefix Corresponds to ../@mp:prefix

Examples
A. Map the OPENXML rowset columns to the metaproperties

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

In the OPENXML statement:

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

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

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

And then, the SELECT statement returns the rowset provided by OPENXML:

DECLARE @idoc int
DECLARE @doc varchar(1000)
-- Sample XML document
SET @doc ='
<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 entire XML document

In this example, OPENXML creates a one-column rowset view of the sample XML document. This column (Col1) is mapped to the xmltext metaproperty, making it an overflow column. Therefore, the column receives the unconsumed data, which is the entire document in this case.

And then the SELECT statement returns the entire rowset.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<?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 entire document without the XML declaration, the query can be specified as:

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

The query returns the root element with the name root and the data contained by the root element

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

This example creates a rowset view of the sample XML document by using OPENXML. 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), indicating attribute-centric mapping and that only the unconsumed data should be copied to the overflow column.

And then the SELECT statement returns the rowset provided by OPENXML.

In this example, @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, indicating 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 @mp:xmltext metaproperty.

DECLARE @idoc int
DECLARE @doc varchar(1000)
-- sample XML document
SET @doc ='
<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 and date columns are already consumed, they do not 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

Writing XML Using OPENXML

OPENXML