How can I import and export data from SQL Server 2005 to XML format?

Question

I am very confused about how SQL Server 2005 supports importing and exporting of XML files. I was thinking that BCP was the way to go. However after reading all of the documentation it seems like there is no way to just take a regular table and export it in XML format. The reason that I am asking is that I am working with a client that will be sending me data updates in XML format. We both have the same database with identical structures. He has sent me example XML files. I was assuming that there would be some very simple way to import the data files but I can't figure it out. Can anyone help me understand how to take an XML file and import the data in to an existing table? It should be simple because the XML file was generated from a table with the exact same structure.

Thanks, Corey

Answer 1

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.

Answer 2

Why on earth use XML as exchange format in the first place?

If you have the exact same data model on both sides plain text files or BCP files will perfectly fit your needs and also be way smaller in size (ie extract and load faster)!

Just because XML is "state of the art" it dosn't meen it is always the best choice! Of course if you belive that you some time in the future need to exchange data with 3rd parties not sharing the same data model or even the same platform or interface the case is of course different.

Answer 3

I assume the client is using FOR XML to wrap the relational data as an xml document? There is no automagic way to shred the xml back to the original relational structure, but you should be able to do it programatically if the original xml generation also uses the XMLSCHEMA directive. If the client includes this, then an xml schema will be generated that describes which column (and its type) is represented where in the xml instance. I started playing with this approach awhile back but never finished it.

Answer 4

Importing XML into SQL Server

Search XML Column in SQL

You have to parse the XML as into a table like format. Remember: xml is just text so you have to tell SQL Server what datatypes are etc.

There is no one shot magic bullet INSERT MyTable FROM File = 'bob.xml'