OPENXML provides a rowset view over an XML document. As 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.
Syntax of OPENXML Function :
OPENXML( idocint [in],rowpatternnvarchar[in],[flagsbyte[in]] ) [ WITH (SchemaDeclaration | TableName) ]
idoc
is a 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
is a XPath pattern used to identify the nodes (in the XML document whose handle is passed in the idoc parameter) to be processed as rows.
flags indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled.
SchemaDeclaration is a schema definition of the form:
ColNameColType [ColPattern | MetaProperty][, ColNameColType [ColPattern | MetaProperty]...]
TableName is 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.
Examples of OPENXML Function :
Example 1 : Use of OPENXML function in select clause
DECLARE @InputDoc VARCHAR(1000)
SET @InputDoc ='
<ROOT>
<Customer CustomerID="1" ContactName="Maria Anders">
<Order OrderID="1" CustomerID="1" EmployeeID="1"
OrderDate="2000-10-01">
<OrderDetail ProductID="1" Quantity="22"/>
<OrderDetail ProductID="77" Quantity="13"/>
</Order>
</Customer>
<Customer CustomerID="10" ContactName="Hanna Moos">
<Order OrderID="2" CustomerID="10" EmployeeID="1"
OrderDate="2000-10-01">
<OrderDetail ProductID="10" Quantity="14"/>
<OrderDetail ProductID="7" Quantity="1"/>
</Order>
</Customer>
</ROOT>'
--Create XML document.
exec sp_xml_preparedocument @OutPutDoc OUTPUT, @InputDoc
-- SELECT statement using OPENXML rowset provider
SELECT *
FROM OPENXML (@OutPutDoc, '/ROOT/Customer/Order/OrderDetail',1)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
WHERE Qty >=10
Output
OrderID CustomerID ContactName OrderDate ProdID Qty
1 1 Maria Anders 2000-10-01 00:00:00.000 1 22
1 1 Maria Anders 2000-10-01 00:00:00.000 77 13
2 10 Hanna Moos 2000-10-01 00:00:00.000 10 14