Loading An XML Document Into SQL Server: Another OPENROWSET Trick

  • Article

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