Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Loading An XML Document Into SQL Server: Another OPENROWSET Trick

Loading An XML Document Into SQL Server: Another OPENROWSET Trick

  • Comments 6
  • Likes

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
  • Back in March, I presented the "second OPENROWSET trick", a technique for loading a flat file into a...

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

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

  • 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:\neilshare\StartHereLogStatApplication\bin\Debug\GenericApplication.xml', SINGLE_BLOB) AS X

  • 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

  • Hi

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment