--- title: "OPENXML (Transact-SQL)" description: OPENXML provides a rowset view over an XML document. author: MikeRayMSFT ms.author: mikeray ms.reviewer: randolphwest ms.date: 06/20/2024 ms.service: sql ms.subservice: t-sql ms.topic: reference f1_keywords: - "OPENXML_TSQL" - "OPENXML" helpviewer_keywords: - "OPENXML statement" - "rowsets [SQL Server], XML documents" - "XML [SQL Server], rowset views" dev_langs: - "TSQL" --- # OPENXML (Transact-SQL) [!INCLUDE [SQL Server Azure SQL Database Azure SQL Managed Instance](../../includes/applies-to-version/sql-asdb-asdbmi.md)] `OPENXML` provides a rowset view over an XML document. Because `OPENXML` is a rowset provider, `OPENXML` can be used in [!INCLUDE [tsql](../../includes/tsql-md.md)] statements in which rowset providers such as a table, view, or the `OPENROWSET` function can appear. :::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: [Transact-SQL syntax conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ```syntaxsql OPENXML ( idoc int [ in ] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) [ WITH ( SchemaDeclaration | TableName ) ] ``` ## 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 [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] 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` or `element-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` and `element-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 node
`2` = Attribute node
`3` = 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 `` 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). ```sql DECLARE @idoc INT, @doc VARCHAR(1000); SET @doc = ' '; --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) ); ``` [!INCLUDE [ssResult](../../includes/ssresult-md.md)] ```output 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. [!INCLUDE [ssResult](../../includes/ssresult-md.md)] ```output 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 `` elements. `ProductID` and `Quantity` are the attributes of the `` element. However, `OrderID`, `CustomerID`, and `OrderDate` are the attributes of the parent element (``). The optional *ColPattern* is specified for the following mappings: - The `OrderID`, `CustomerID`, and `OrderDate` 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 the `ProductID` attribute, and the `Qty` column in the rowset maps to the `Quantity` 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. ```sql DECLARE @idoc INT, @doc VARCHAR(1000); SET @doc = ' v '; --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' ); ``` [!INCLUDE [ssResult](../../includes/ssresult-md.md)] ```output 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 ``, ``, and `` 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 `` 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. ```sql DECLARE @idoc INT, @doc VARCHAR(1000); SET @doc = ' '; --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 - [Examples: Using OPENXML](../../relational-databases/xml/examples-using-openxml.md)