SQL Server XML Shredding Part 2


Using SQL Server, how can we load an XML file into a variable? In Part 1 we demonstrated how to take an XML variable and run a select statement against the XML variable. We were able to focus on any part of the XML that we wanted.

XML File into a Variable

Below is the code to load a file into a variable. Here, we know the location of the file and the file name, so we can just hard-code it into our select statement.

DECLARE @xml XML
SET @xml = 
(
  SELECT *
  FROM OPENROWSET(BULK 'C:\data.xml', SINGLE_BLOB) AS data
)

XML File to a single XML Column in a Table

Assuming we have a table with a column with type XML, then we can use the following SQL command to import a file ‘testdata.txt’ into that column.

Insert into MyTestImportTable(XmlColumn)
   select * from OpenRowSet( bulk 'c:\xml\bookstoresample.xml', single_blob ) as X;

Here is our XML file that we need to import.

<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
  <book category="cooking">
    <title lang="en">Everyday Italian</title>
    <author>Giada De Laurentiis</author>
    <year>2005</year>
    <price>30.00</price>
  </book>
  <book category="children">
    <title lang="en">Harry Potter</title>
    <author>J K. Rowling</author>
    <year>2005</year>
    <price>29.99</price>
  </book>
</bookstore>

What if we want to specify the location and file name with a variable? We will need to use dynamic SQL.

Series Navigation<< SQL Server XML Shredding Part 1SQL Server XML Shredding Part 3 >>

Leave a comment

Your email address will not be published. Required fields are marked *