none
Import XML (with schema) to Tables in SQL Server 2008

    Question

  • Hi,

    When I need to import data from XML to tables in SQL Server. Normally I use the script like below. But if I have many tables need to do the same thing, it will take long time to write the script due to different table has diferent table schema. It's hard to dynamic to generate script for WITH (SchemaDeclaration) clause.

    Can I use XML with inline XSD schema to import data based on this schema to tables without writing WITH clause. So I can write generic script to load all XMLs rather than writing static script to import one by one.

    Questions:
    Can I do these directly by TSQL Scripts? If not, how to do it in the other way?

    Please provide information as more detail as possible. Thanks.


    create table customer( CustomerID  varchar(10), ContactName varchar(20))
    GO
    
    DECLARE @idoc int
    DECLARE @doc varchar(1000)
    SET @doc ='
    <ROOT>
    <Customer CustomerID="VINET" ContactName="Paul Henriot">
    </Customer>
    <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
    </Customer>
    </ROOT>'
    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    -- Execute a SELECT statement that uses the OPENXML rowset provider.
    INSERT INTO customer (customerID,contactName)
    SELECT    *
    FROM       OPENXML (@idoc, '/ROOT/Customer',1)
                WITH (CustomerID  varchar(10),
                      ContactName varchar(20))
    
    select * from customer


    Helen
    Wednesday, July 15, 2009 11:42 PM

Answers

  • You can use SQL XML Bulk Load to do this, but you have to split the XSD out into separate files. Look in Books Online (BOL) for more information. Start here:

    http://msdn.microsoft.com/en-us/library/ms171993.aspx
    http://support.microsoft.com/kb/316005
    • Marked as answer by SeaCloud Thursday, July 16, 2009 11:48 PM
    Thursday, July 16, 2009 2:37 PM

All replies

  • You can use SQL XML Bulk Load to do this, but you have to split the XSD out into separate files. Look in Books Online (BOL) for more information. Start here:

    http://msdn.microsoft.com/en-us/library/ms171993.aspx
    http://support.microsoft.com/kb/316005
    • Marked as answer by SeaCloud Thursday, July 16, 2009 11:48 PM
    Thursday, July 16, 2009 2:37 PM
  • Thanks wBob. That's the links I need. I will try to make it.
    Helen
    Thursday, July 16, 2009 11:49 PM