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.