Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Rowset Functions RSS Feeds

OPENXML Function

Posted By: Sarita Patel     Category: SQL Server     Views: 5512

This article explains about OPENXML function in sql server with examples.

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 i
s 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 i
s 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 @OutPutDoc INT
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
  
Share: 

 
 
 

Didn't find what you were looking for? Find more on OPENXML Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of OPENXML Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!