Events
Get certified in Microsoft Fabric—for free!
19 Nov, 11 pm - 10 Dec, 11 pm
For a limited time, the Microsoft Fabric Community team is offering free DP-600 exam vouchers.
Prepare nowThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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 isn't 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 |
The following examples illustrate how OPENXML is used to create different rowset views.
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
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
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"/>
Events
Get certified in Microsoft Fabric—for free!
19 Nov, 11 pm - 10 Dec, 11 pm
For a limited time, the Microsoft Fabric Community team is offering free DP-600 exam vouchers.
Prepare now