Loading An XML Document Into SQL Server: Another OPENROWSET Trick


Have you ever had an XML document in a file that you needed to get into a database column quick-and-dirty?  Well, another OPENROWSET trick is ready to come to our rescue.


Up until I was shown this trick, every time I needed to import XML into a data column, I’d load up a copy of the XML in SQL Server Management Studio and build a simple INSERT statement around the XML.


I like this approach much better:


CREATE TABLE MyTable
(
    MyTableId INT IDENTITY
,
    MyXml
XML
)


INSERT MyTable(MyXml)
SELECT * FROM OPENROWSET
(
    BULK ‘C:\Documents and Settings\wardp\Desktop\xmlResult1.xml’
,
    SINGLE_BLOB
) AS
X

SELECT
* FROM MyTable


You can of course adapt this approach to whatever fiendish purpose you might have, but at least we’re out of the business of hand-crafted INSERT statements.


If you’ve found a slicker way to address this requirement, I’d love to hear it!


     -wp

Comments (6)

  1. Anonymous says:

    Hi Neil..

    What version(s) of SQL Server did you test this on?  This is working for me on both SQL Server 2005 and SQL Server 2008.

        -wp

  2. Anonymous says:

    Back in March, I presented the "second OPENROWSET trick", a technique for loading a flat file into a…

  3. Anonymous says:

    Bert left a comment on this post pointing out a limitation of “the other OPENROWSET trick” (the one which

  4. Bert says:

    I tried this, but had an error because my XML file is 6 Gb. The SINGLE_BLOB seems to be limitd to 2 Gb …

  5. Neil says:

    Hi

     I tried this method above but I cannot get it to work keep getting the error below:

    Server: Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword ‘BULK’.

    Tried taking the bulk out but then it fails on the single_blob type. Can you give some help to why it is not working cheers. The code is same as above:

    INSERT GenericXmlTbl(AppXml)

    SELECT * FROM OPENROWSET(BULK ‘D:neilshareStartHereLogStatApplicationbinDebugGenericApplication.xml’, SINGLE_BLOB) AS X

  6. Neil Rastogi says:

    Hi

    Thanks for responding. The version I am using is sql server 2000 do you think this could be the issue?