Programmer can use the rowset created by openxml to store the data, in the same way that you would use any other rowset. You can insert the rowset data into permanent tables in a database. For example, you can insert the data received by a supplier in the XML format into the SalesOrderHeader and SalesOrderDetail tables.
Consider an example where customers shop online and the order given by the customers are transferred to the supplier in the form of an XML document. Following is the data available in the XML document:
DECLARE @Doc int
DECLARE @XMLDoc nvarchar (1000)
SET @XMLDoc = N’ <ROOT>
<Customer CustomerID=”JH01” ContactName=”John Henriot”>
<Order OrderID=”1001 CustomerID=”JH01”
<OrderDate=”2006-07-04T00:00:00”>
<OrderDetail ProductID=”11” Quantity=”12”/>
<OrderDetail ProductID=”22” Quantity=”10”/>
<Order>
</Customer>
<Customer CustormerID=”SG01” ContactName=”Steve Gonzlez”>
<Order OrderID=”1002” CustomerID=”SG01”
OrderDate=”2006-08-16T00:00:00”>
<OrderDetail ProductID=”32” Quantity=”3”/>
</Order>
</Customer>
</ROOT>’
To view this XML data in a rowset, you need to execute the following statements:
The data that will be displayed as shown in the following table.
CustomerID ContactName
JH01 John Henriot
SG01 Steve Gonzlez
You can also specify the column pattern to map the rowset columns and the XML attributes and elements. You can use the following OPENXML statement with the preceding statements to specify the column pattern:
SELECT *
FROM openxml (@Doc, ‘/ROOT/Customer/Order/OrderDetail’,1)
WITH (CustomerID varchar (10) ‘../../@CustomerID’,
ContactName vchar (20)’../../@ContactName’, OrderID int ‘../@OrderID’,
OrderDate datetime ‘ ../@OrderDate’, ProdID int ‘@ProductID’, Quality int)
Clearing the Memory
After saving the data permanently in the database, you need to release the memory where you stored the rowset. For this, you can use the sp_xml_removedocument stored procedure.Consider an example where customers shop online and the order given by the customers are transferred to the supplier in the form of an XML document. Following is the data available in the XML document:
DECLARE @Doc int
DECLARE @XMLDoc nvarchar (1000)
SET @XMLDoc = N’ <ROOT>
<Customer CustomerID=”JH01” ContactName=”John Henriot”>
<Order OrderID=”1001 CustomerID=”JH01”
<OrderDate=”2006-07-04T00:00:00”>
<OrderDetail ProductID=”11” Quantity=”12”/>
<OrderDetail ProductID=”22” Quantity=”10”/>
<Order>
</Customer>
<Customer CustormerID=”SG01” ContactName=”Steve Gonzlez”>
<Order OrderID=”1002” CustomerID=”SG01”
OrderDate=”2006-08-16T00:00:00”>
<OrderDetail ProductID=”32” Quantity=”3”/>
</Order>
</Customer>
</ROOT>’
To view this XML data in a rowset, you need to execute the following statements:
- Create an internal representation of the XML document by executing the following statement:
EXEC sp_xml_preparedocumnt @Doc OUTPUT, @XMLDoc - Execute the following query to store the data in a table by using the OPENXML function:
INSERT INTO CustomerDetails
SELECT *
FROM openxml (@Doc, ‘/ROOT/Customer’, 1)
WITH (CustomerID varchar (10), ContactName varchar (20) )
The data that will be displayed as shown in the following table.
CustomerID ContactName
JH01 John Henriot
SG01 Steve Gonzlez
- Remove the internal tree from the memory by executing the following statement:
EXEC sp_xml_removedocument @Doc
You can also specify the column pattern to map the rowset columns and the XML attributes and elements. You can use the following OPENXML statement with the preceding statements to specify the column pattern:
SELECT *
FROM openxml (@Doc, ‘/ROOT/Customer/Order/OrderDetail’,1)
WITH (CustomerID varchar (10) ‘../../@CustomerID’,
ContactName vchar (20)’../../@ContactName’, OrderID int ‘../@OrderID’,
OrderDate datetime ‘ ../@OrderDate’, ProdID int ‘@ProductID’, Quality int)