SQL Server 2005 allows to store XML data in two ways:
- As a Rowset &
- An XML Column
Storing XML Data in a Rowset
- If your client has sent you data as an
XML document and you need to store
this data in a database table, first
you need to "shred" the XML data. SQL
Server allows you to shred XML data
by using the OPENXML function and
its related stored procedures.
Shredding XML document involves the following tasks:
- i) Parse the XML document SQL
server 2005 provides the
sp_xml_preparedocument stored procedure to parse the xml document.
This stored procedure reads the xml
document and parses it with the MSXML
parser. The parsed document is an
internal tree representation of
various nodes in the xml doc such as
elements,attributes,text and
comments.
- ii)Retrieve the rowset from the tree.
Now you need to extract the data from
the available xml data. You use
openxml function for this purpose and to generate an in-memory rowset
from the parsed data. Syntax:
openxml(idoc int[in],rowpattern
nvarchar[in],[flags byte[in]]) [WITH
(SchemaDeclaration | TableName)]
idoc- specifies the doc handle of the
internal tree representation of xml
doc rowpattern- specifies the XPath
pattern used to identify the nodes to
be processed as rows. flags-
indicates the mapping that should be
used between xml data and relational
rowset, and how the spill-over column
should be filled. It is optional and
can have 0,1,2,3 to use default
mapping, to retrieve attribute
values, to retrieve element values
and to retrieve both values
respectively. SchemaDeclaration-
specifies the rowset schema
declaration for the columns to be
returned by using a combination of
column names, data types and
patterns. TableName- specifies the
table name that can be given instead
of SchemaDeclaration, if a table with
the desired schema already exists and
no patterns are required.
- iii)Store the data from the rowset.
You can use the rowset created by
openxml to store the data, in the same way that you would sue any other
rowset. You can insert the rowset
data into permanent tables in a
database.
- iv)Clear the memory. You need to
release the memory where you stored
the rowset. For this, you use the
sp_xml_removedocument stored procedure.
For example, following is the data available in an XML doc:
DECLARE @Doc int
DECLARE @XMlDoc nvarchar(1000)
SET @XMLDoc = N'<ROOT>
<Customer CustomerID="JHO1" Name="Jack">
<Order OrderID="1001" CustomerID="JH01"
OrderDate="2009-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="22" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="SG01" Name="Steve">
<Order OrderID="1002" CustomerID="SG01"
OrderDate="2009-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:
1. Create internal representation of xml document EXEC sp_xml_preparedocument @Doc OUTPUT, @XMLDoc
2. Execute the following query to store the data in a table using OPENXML function:
INSERT INTO CustomerDetails
SELECT *
FROM openxml (@Doc, '/ROOT/Customer' , 1)
WITH (CustomerID varchar(10),
Name varchar(20) )
The data will be displayed as in the table:
CustomerID | Name |
___________|_________|
JH01 | Jack |
| |
SG01 | Steve |
___________|_________|
3. Remove the internal tree from the memory by executing
EXEC sp_xml_removedocument @Doc
You're done now.
I think this method would help rather than the other one, ie, storing xml data as XML Column.