Specify Metaproperties in OPENXML
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 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. 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 are 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.
Note
You cannot reference these metaproperties in any XPath navigation.
Metaproperty attribute |
Description |
|---|---|
@mp:id |
Provides a system-generated, document-wide identifier of the DOM node. As long as the document is not reparsed, this ID refers to the same XML node. 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 a prefix and a namespace URI 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, it 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. This provides 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 front of the sibling list, @mp:prev is NULL. |
@mp:xmltext |
Used for processing purposes. It is the textual serialization of the element and its attributes, and also the subelements, as used in the overflow handling of OPENXML. |
This table shows the additional parent properties that are provided and which 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
The following examples illustrate how OPENXML is used to create different rowset views.
A. Mapping 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. Retrieving 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 is 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 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"/>