Using OpenRowSet for loading up an XML document

We have written a lot about XML features in both Oracle and SQL Server and have also written about OpenRowSet and OpenDataSource to load up the data from a text file or a xls file.  In response to that question a reader had asked whether we can use OpenRowSet for loading up XML data – she had a file which contained a single XML data structure in it and she wanted to load it up into SQL Server.  It can definitely be done.  Here is an example:

Suppose we have this XML file under C:\Blog folder.  We can then use this code to get the value assigned to a local variable of XML data type:

USE DECIPHERTEST
GO
DECLARE @XML XML;

SELECT @XML = IV.BulkColumn
from OpenRowSet (BULK ‘C:\Blog\Demo_Data.xml’, SINGLE_BLOB) AS IV;

And once we have done that, we can easily de-construct that XML by querying this variable using the nodes() method or other ways of reading the data from an XML variable and then load up the data or do joins with other tables and process the data that way.  If you have a very large XML document, we would recommend using SQLXML Bulk Load rather than using OpenRowSet or other methods.