Import XML data to SQL Server

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!