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


OPENROWSET (Transact-SQL)


-David

Comments (7)

  1. Leo says:

    Estudiante de Ingenieria de Sistemas

  2. This is the second in a series of post.  You can find the first post here.

    Now that we’ve loaded…

  3. This is the third in a series of posts.  You can find the previous posts here (1, 2)….

  4. This is the third in a series of posts.  You can find the previous posts here (1, 2, 3)….

  5. sundeep raina says:

    i want to load bpel file (xml ) file into sql server 2005 express that it gives me windows workflow design