Post reply

Bifurcating XML data into various tables

  • September 20, 2010 at 6:45 am

    #103989

    hi,

    I am trying to import an XML file into SQL server 2005. Will it not create the tables and required columns automatically looking at the structure.

    How to go about it.I have no clue...

    Please help 🙁

    Here is what i tried...

    (Note that the XML file is quite big approx 300mb)

    CREATE TABLE XmlImportTest(xmlFileName VARCHAR(300) NOT NULL,xml_data XML NOT NULL)

    GO

    DECLARE @xmlFileName VARCHAR(300)

    SELECT @xmlFileName = 'c:\DUMP_20APRIL.xml'

    EXEC('INSERT INTO XmlImportTest(xmlFileName, xml_data)

    SELECT '''+ @xmlFileName + ''', xmlData FROM(SELECT *

    FROM OPENROWSET (BULK '''+ @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA) AS FileImport (XMLDATA)')

    --GO

  • September 21, 2010 at 7:45 am

    #1224820

    hey people,

    Please reply...

    Thanks

  • September 21, 2010 at 2:07 pm

    #1225214

    I'm not sure what you're struggling with...

    After changing your sample code pointing to a file that exists in my system your code did run just fine. I had one row in the XmlImportTest table. The first column has the xml file name as specified in @xmlFileName and the second column has the content of the file.

    What result do you expect?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply