Loading XML Documents into SQL Server 2005 - Part 1

One of the new scenarios enabled by SQL Server 2005 is the ability to store and search XML documents.  This series of posts will describe techniques for loading the XML documents into the database.

To start with, we will simply load the document from a file into a TSQL variable.

The simple xml document I am using can be found here. I named it "doc1.xml" and put it in the c:\temp directory on my database server.

The code to load the document into a TSQL variable can be found here. You'll notice that it uses the openrowset bulk provider to load the file.  The SINGLE_CLOB option is used to load the file all at once, instead of a row at a time. 

If you execute the sql script from a client, keep in mind that the code runs on the server.  This means that the xml document must be in the c:\temp directory on the server, not the client where you are running the script.

For more info, see the following books online topics:

Bulk Importing and Exporting XML Documents