Jaa

Loading An XML Document Into SQL Server: Another OPENROWSET Trick

  • Artikkeli

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

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    Back in March, I presented the "second OPENROWSET trick", a technique for loading a flat file into a...

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

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

  • Anonymous
    July 24, 2009
    The comment has been removed

  • Anonymous
    July 27, 2009
    Hi Thanks for responding. The version I am using is sql server 2000 do you think this could be the issue?