OPENXML (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
OPENXML
provides a rowset view over an XML document. Because OPENXML
is a rowset provider, OPENXML
can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET
function can appear.
Transact-SQL syntax conventions
Syntax
OPENXML ( idoc int [ in ]
, rowpattern nvarchar [ in ]
, [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
idoc
The document handle of the internal representation of an XML document. The internal representation of an XML document is created by calling sp_xml_preparedocument
.
rowpattern
The XPath pattern used to identify the nodes to be processed as rows. The nodes come from the XML document whose handle is passed in the idoc parameter.
flags
Indicates the mapping used between the XML data and the relational rowset, and how the spill-over column is filled. flags is an optional input parameter, and can be one of the following values.
Byte value | Description |
---|---|
0 |
Defaults to attribute-centric mapping. |
1 |
Use the attribute-centric mapping. Can be combined with XML_ELEMENTS . In this case, attribute-centric mapping is applied first. Next, element-centric mapping is applied for any remaining columns. |
2 |
Use the element-centric mapping. Can be combined with XML_ATTRIBUTES . In this case, element-centric mapping is applied first. Next, attribute-centric mapping is applied for any remaining columns. |
8 |
Can be combined (logical OR) with XML_ATTRIBUTES or XML_ELEMENTS . In the context of retrieval, this flag indicates that the consumed data shouldn't be copied to the overflow property @mp:xmltext . |
SchemaDeclaration
A schema definition of the form: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ]
ColName
The column name in the rowset.
ColType
The SQL Server data type of the column in the rowset. If the column types differ from the underlying xml data type of the attribute, type coercion occurs.
ColPattern
An optional, general XPath pattern that describes how the XML nodes should be mapped to the columns. If ColPattern isn't specified, the default mapping (
attribute-centric
orelement-centric
mapping as specified by flags) takes place.The XPath pattern specified as ColPattern is used to specify the special nature of the mapping (for
attribute-centric
andelement-centric
mapping) that overwrites or enhances the default mapping indicated by flags.The general XPath pattern specified as ColPattern also supports the metaproperties.
MetaProperty
One of the metaproperties provided by
OPENXML
. If MetaProperty is specified, the column contains information provided by the metaproperty. The metaproperties allow you to extract information (such as relative position and namespace information) about XML nodes. These metaproperties provide more information than is visible in the textual representation.
TableName
The table name that can be given (instead of SchemaDeclaration), if a table with the desired schema already exists and no column patterns are required.
Remarks
The WITH
clause provides a rowset format (and additional mapping information as required) by using either SchemaDeclaration or specifying an existing TableName. If the optional WITH
clause isn't specified, the results are returned in an edge table format. Edge tables represent the fine-grained XML document structure (such as element/attribute names, the document hierarchy, the namespaces, PIs, and so on) in a single table.
The following table describes the structure of the edge table.
Column name | Data type | Description |
---|---|---|
id |
bigint | The unique ID of the document node. The root element has an ID value 0 . The negative ID values are reserved. |
parentid |
bigint | Identifies the parent of the node. The parent identified by this ID isn't necessarily the parent element, but it depends on the nodetype of the node whose parent is identified by this ID. For example, if the node is a text node, the parent of it might be an attribute node.If the node is at the top level in the XML document, its ParentID is NULL . |
nodetype |
int | Identifies the node type. This value is an integer that corresponds to the XML DOM node type numbering. The node types are:1 = Element node2 = Attribute node3 = Text node |
localname |
nvarchar | Gives the local name of the element or attribute. NULL if the DOM object doesn't have a name. |
prefix |
nvarchar | The namespace prefix of the node name. |
namespaceuri |
nvarchar | The namespace URI of the node. If the value is NULL , no namespace is present. |
data type |
nvarchar | The actual data type of the element or attribute row, otherwise is NULL . The data type is inferred from the inline DTD or from the inline schema. |
prev |
bigint | The XML ID of the previous sibling element. NULL if there's no direct previous sibling. |
text |
ntext | Contains the attribute value or the element content in text form (or is NULL if the edge table entry doesn't require a value). |
Examples
A. Use a basic SELECT statement with OPENXML
The following example creates an internal representation of the XML image by using sp_xml_preparedocument
. A SELECT
statement that uses an OPENXML
rowset provider is then executed against the internal representation of the XML document.
The flag value is set to 1
. This value indicates attribute-centric
mapping. Therefore, the XML attributes map to the columns in the rowset. The rowpattern specified as /ROOT/Customer
identifies the <Customers>
nodes to be processed.
The optional ColPattern (column pattern) parameter isn't specified because the column name matches the XML attribute names.
The OPENXML
rowset provider creates a two-column rowset (CustomerID
and ContactName
) from which the SELECT
statement retrieves the necessary columns (in this case, all the columns).
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer', 1) WITH (
CustomerID VARCHAR(10),
ContactName VARCHAR(20)
);
Here is the result set.
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
If the same SELECT
statement is executed with flags set to 2
, indicating element-centric
mapping, the values of CustomerID
and ContactName
for both of the customers in the XML document are returned as NULL
, because there aren't any elements named CustomerID
or ContactName
in the XML document.
Here is the result set.
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B. Specify ColPattern for mapping between columns and the XML attributes
The following query returns customer ID, order date, product ID, and quantity attributes from the XML document. The rowpattern identifies the <OrderDetails>
elements. ProductID
and Quantity
are the attributes of the <OrderDetails>
element. However, OrderID
, CustomerID
, and OrderDate
are the attributes of the parent element (<Orders>
).
The optional ColPattern is specified for the following mappings:
The
OrderID
,CustomerID
, andOrderDate
in the rowset map to the attributes of the parent of the nodes identified by rowpattern in the XML document.The
ProdID
column in the rowset maps to theProductID
attribute, and theQty
column in the rowset maps to theQuantity
attribute of the nodes identified in rowpattern.
Although the element-centric
mapping is specified by the flags parameter, the mapping specified in ColPattern overwrites this mapping.
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer/Order/OrderDetail', 2) WITH (
OrderID INT '../@OrderID',
CustomerID VARCHAR(10) '../@CustomerID',
OrderDate DATETIME '../@OrderDate',
ProdID INT '@ProductID',
Qty INT '@Quantity'
);
Here is the result set.
OrderID CustomerID OrderDate ProdID Qty
------------------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
C. Obtain results in an edge table format
The sample XML document in the following example consists of <Customers>
, <Orders>
, and <Order_0020_Details>
elements. First, sp_xml_preparedocument
is called to obtain a document handle. This document handle is passed to OPENXML
.
In the OPENXML
statement, the rowpattern (/ROOT/Customers
) identifies the <Customers>
nodes to process. Because the WITH
clause isn't provided, OPENXML
returns the rowset in an edge table format.
Finally the SELECT
statement retrieves all the columns in the edge table.
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML(@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc;
Related content
الملاحظات
https://aka.ms/ContentUserFeedback.
قريبًا: خلال عام 2024، سنتخلص تدريجيًا من GitHub Issues بوصفها آلية إرسال ملاحظات للمحتوى ونستبدلها بنظام ملاحظات جديد. لمزيد من المعلومات، راجعإرسال الملاحظات وعرضها المتعلقة بـ