A. Using a simple 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 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 is not 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
DECLARE @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 the <Customers> elements do not have any subelements.
Here is the result set.
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B. Specifying 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. This indicates the following:
-
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.
DECLARE @idoc int
DECLARE @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">
<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. Obtaining 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 is not 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
DECLARE @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