In this article I’m going to provide an example of how you can read data from an XML file in SQL Server.
To achieve this we’re going to use OPENROWSET and OPENXML functions.
In this example I’m using the XML sample books which you can obtain from here.
Open Row Set
Using the open row set function you can read the contents of the file. In this example I’ve converted the column to the XML data type.
SELECT CONVERT(XML, BulkColumn)
FROM OPENROWSET (BULK 'C:\books.xml', SINGLE_BLOB) AS A
Open XML
Now we’re able to read the contents of the file into SQL we want to be able to get the data into a tabular format. To achieve this we want to do two things, prepare the XML document, and then open into a tabular format.
To prepare the XML content we want to use the sp_xml_preparedocument system stored procedure.
You’ll notice in the below SQL that I’m using the OPENXML function and the WITH syntax to specify the columns. At the end of each column I’m specifying a ColPattern in order to get the element I want.
DECLARE @doc xml
,@idoc int
SET @doc =
(SELECT CONVERT(xml, BulkColumn)
FROM OPENROWSET (BULK 'C:\books.xml'
, SINGLE_BLOB) AS A )
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
SELECT *
FROM OPENXML(@idoc,'/catalog/book',1)
WITH
(
id varchar(100) '@id'
,author varchar(100) './author'
,title varchar(100) './title'
,genre varchar(100) './genre'
,price varchar(100) './price'
,publish_date varchar(100) './publish_date'
,[description] varchar(100) './description'
)
EXEC sp_xml_removedocument @idoc
From experience I also tend to specify a varchar or nvarchar data type in order to handle bad data after staging to a table.
I hope this is helpful. Enjoy!
