Wednesday, March 26, 2014

OPENXML Function - Read XML file in SQL Server 2008

Here I will explain how to read data from xml document and insert it into SQL table in SQL Server 2008.

The examples in this post show how OPENXML function is used to create a rowset view of an XML document.

Example:

For reading data from the xml document, like row pattern is used to identify the nodes(its also identify same name nodes) in the XML document. For example, if the pattern(nodes like '/Customer/order') ends in an element or an attribute, a row is created for each element or attribute node that is selected by row pattern.

The OPENXML statement represents the following:

  •     Row pattern (/Customer/order) identifies the nodes to process.
  •     For attribute-centric flags parameter value is set to 1. As an output, the XML attributes map to the columns in the rowset defined in Schema Declaration.

The XML document in this example is contain of  <customer>, <order>, and <orderdetail> elements.

Example 1: Use of OPENXML function in select statement

DECLARE @DocHandle int
Declare @XML NVARCHAR(MAX)
SET @XML = '<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer> </ROOT>'

--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Execute a SELECT statement using OPENXML.
SELECT * 
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')
 

The SELECT statement is used to retrieves all the columns in the rowset provided by OPENXML.

Output:


From this result you can insert all that data into your SQL table using below query you can just need to  put insert query above select query with all that columns that you need to be insert into the table.

Example 2 : Use of OPENXML function to insert data into SQL table


DECLARE @DocHandle int
Declare @XML NVARCHAR(MAX)
SET @XML = '<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer> </ROOT>'

--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Insert data from SELECT statement using OPENXML.
INSERT INTO CustomerOrder(OrderID,CustomerID,ContactName,OrderDate,
ProductID,Qty,Amount,Comment)
SELECT OrderID, CustomerID, ContactName, OrderDate, ProductID, Qty,
Amount, Comment
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')
 

After execute above query you get your data into you SQL table.

Examlpe 3 : Read XML file and use of OPENXML function in select statement
  

DECLARE @DocHandle int
Declare @XML XML
--Read XML file from you local and insert your data very easy and fast(bulk) 
 SET @XML = 
(SELECT * FROM OPENROWSET(BULK 'd:\test.xml',
 SINGLE_BLOB) AS x)
--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Execute a SELECT statement using OPENXML.
SELECT * 
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')
 

Output:



test.xml
<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer>
<Customer CustomerID="3" ContactName="shivani bhavsar">
   <Order OrderID="103" CustomerID="3" OrderDate="2014-03-01">
      <OrderDetail ProductID="21" Quantity="18" Amount="280$"/>
      <OrderDetail ProductID="9" Quantity="5" Amount="80$"/>
   </Order>
</Customer>
</ROOT>


I have give above file location in SQL query to read data of this file and main benefit of using xml file is that you can give big xml file(about 150MB) to get execute easily and very fast.

-Thank you   

No comments:

Post a Comment