Systems Engineering and RDBMS

Using OpenRowSet for loading up an XML document

Posted by decipherinfosys on February 13, 2009

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.

2 Responses to “Using OpenRowSet for loading up an XML document”

  1. Martin said

    Hi,
    thanks for this, this really helped to load an XML-file directly from file system to use it in sp_xml_preparedocument procedure!

  2. Paul said

    Is there a way to use OpenRowSet to load a remote XML file over HTTP rather than a local file?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: