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