Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Importing and Processing data from XML files into SQL Server tables

MSSQLTips author Arshad Ali By:   |   Read Comments (19)   |   Related Tips: More > XML
Problem

In my last article, I talked about how you can use an  FTP task in SSIS to download files from an FTP server. But what if the file you have downloaded is an XML file and you need to import this data from the XML file into a SQL Server table? How do you process/parse XML data into SQL Server tables?

Solution

There are different ways to achieve this task of importing data from an XML file into a SQL Server table, but I am going to demonstrate one of easiest ways to accomplish this task.

These are the steps I performed for importing data into SQL Server and then parsing the XML into a relational format.

  • Import XML data from an XML file into SQL Server table using the OPENROWSET function
  • Parse the XML data using the OPENXML function

Importing XML data from XML file using OPENROWSET

I have an XML file downloaded from my FTP location to a local folder and data in this XML file looks like this:

Importing XML data from XML file using OPENROWSET

Now in order to import data from the XML file to a table in SQL Server, I am using the OPENROWSET function as you can see below.

In the script below, I am first creating a table with a column of data type XML and then reading the XML data from the file using the OPENROWSET function by specifying the file location and name of the XML file as you can see below: 

CREATE DATABASE OPENXMLTesting
GO

USE OPENXMLTesting GO
CREATE TABLE XMLwithOpenXML ( Id INT IDENTITY PRIMARY KEY, XMLData XML, LoadedDateTime DATETIME )
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime) SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;
SELECT * FROM XMLwithOpenXML

When I query the table in which I have imported the XML data, it looks like this. The XMLData column is an XML data type, it will output a hyperlink as shown below:

As XMLData column is of XML data type, it will give an hyperlink

Clicking on the hyperlink, in the above image, will open another tab within SSMS with the XML data displayed as shown below.

<ROOT>
<Customers>
<Customer CustomerID="C001" CustomerName="Arshad Ali">
<Orders>
<Order OrderID="10248" OrderDate="2012-07-04T00:00:00">
<OrderDetail ProductID="10" Quantity="5" />
<OrderDetail ProductID="11" Quantity="12" />
<OrderDetail ProductID="42" Quantity="10" />
</Order>
</Orders>
<Address> Address line 1, 2, 3</Address>
</Customer>
<Customer CustomerID="C002" CustomerName="Paul Henriot">
<Orders>
<Order OrderID="10245" OrderDate="2011-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12" />
<OrderDetail ProductID="42" Quantity="10" />
</Order>
</Orders>
<Address> Address line 5, 6, 7</Address>
</Customer>
<Customer CustomerID="C003" CustomerName="Carlos Gonzlez">
<Orders>
<Order OrderID="10283" OrderDate="2012-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3" />
</Order>
</Orders>
<Address> Address line 1, 4, 5</Address>
</Customer>
</Customers>
</ROOT>

Process XML data using OPENXML function

Now as I said before, XML data stored in a column of data type XML can be processed either by using XML functions available in SQL Server or by using the sp_xml_preparedocument stored procedure along with the OPENXML function.

We will first call the sp_xml_preparedocument stored procedure by specifying the XML data which will then output the handle of the XML data that it has prepared and stored in internal cache.

Then we will use the handle returned by the sp_xml_preparedocument stored procedure in the OPENXML function to open the XML data and read it.

Note: the sp_xml_preparedocument stored procedure stores the XML data in SQL Server's internal cache, it is essential to release this stored XML data from internal cache by calling the sp_xml_removedocument stored procedure. We should call the sp_xml_removedocument stored procedure as early possible, so that internal cache can be freed for other usage.

USE OPENXMLTesting
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address FROM OPENXML(@hDoc, 'ROOT/Customers/Customer') WITH ( CustomerID [varchar](50) '@CustomerID', CustomerName [varchar](100) '@CustomerName', Address [varchar](100) 'Address' )
EXEC sp_xml_removedocument @hDoc GO

From the above XML data, I want to retrieve all the customer information and hence I am navigating to the Customer element and querying CustomerID and CustomerName (please note the use of "@" before the name of the attribute) attributes and Address element in the above SELECT statement using the OPENXML function.

The structure of the resultset can be determined with the "WITH" clause as shown above.

Process XML data using OPENXML function

From the above XML data, I now want to retrieve all the customer information along with OrderID and OrderDate placed by each individual customer and hence I am navigating to the Order element and then querying OrderID and OrderDate attributes.

If we want to navigate back to the parent or grand parent level and get data from there, we need to use "../" to read the parent's data and "../../" to read the grand parent's data and so on.

USE OPENXMLTesting
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address, OrderID, OrderDate FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order') WITH ( CustomerID [varchar](50) '../../@CustomerID', CustomerName [varchar](100) '../../@CustomerName', Address [varchar](100) '../../Address', OrderID [varchar](1000) '@OrderID', OrderDate datetime '@OrderDate' )
EXEC sp_xml_removedocument @hDoc GO

The result of the above query can be seen in the image below. You can see below all the customers and all the orders placed by each customer.

querying CustomerID and CustomerName

Now let's go one level deeper. This time from the above XML data, I want to retrieve all the customer information and their orders along with ProductID and Quantity from each order placed. And hence, as you can see below I am navigating to the OrderDetail and retrieving the ProductID and Quantity attributes' values. At the same time I am using "../" to reach the parent level to get Order information available at the parent level whereas I am using "../../../" to reach to the great grand parent level to grab Customer information as shown below:

USE OPENXMLTesting
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address, OrderID, OrderDate, ProductID, Quantity FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail') WITH ( CustomerID [varchar](50) '../../../@CustomerID', CustomerName [varchar](100) '../../../@CustomerName', Address [varchar](100) '../../../Address', OrderID [varchar](1000) '../@OrderID', OrderDate datetime '../@OrderDate', ProductID [varchar](50) '@ProductID', Quantity int '@Quantity' )
EXEC sp_xml_removedocument @hDoc GO

The result of the above query can be seen in the image below. You can see all the customer information and their orders along with ProductID and Quantity from each order placed.

The result of the above query
Next Steps


Last Update: 3/19/2013


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Tuesday, March 19, 2013 - 10:34:54 AM - Cary Davis Read The Tip

Nice article Arshad.  


Tuesday, March 19, 2013 - 5:35:52 PM - Rajesh Read The Tip

Very good article Arshad. I played little bit - really helpful for my upcoming project. Thanks -- Rajesh


Wednesday, March 20, 2013 - 6:52:24 AM - manoj Read The Tip

nice article arshad...very helpful

 


Monday, April 22, 2013 - 2:17:50 AM - muruga Read The Tip


<ROOT>
 <Customers>
  <Customer CustomerName="Arshad Ali" CustomerID="C001">
   <Orders>
    <Order OrderDate="2012-07-04T00:00:00" OrderID="10248">
     <OrderDetail Quantity="5" ProductID="11"/>
     <OrderDetail Quantity="12" ProductID="11"/>
     <OrderDetail Quantity="10" ProductID="42"/>
    </Order>
   </Orders>
   <Address>Address line 1,2,3</Address>
  </Customer>

  <Customer CustomerName="Paul Henriot" CustomerID="C002">
   <Orders>
    <Order OrderDate="2011-07-04T00:00:00" OrderID="10245">
     <OrderDetail Quantity="12" ProductID="11"/>
     <OrderDetail Quantity="10" ProductID="42"/>
    </Order>
   </Orders>
   <Address>Address line 5,6,7</Address>
  </Customer>
 </Customers>
</ROOT>


Wednesday, April 24, 2013 - 2:48:20 AM - Reza Hossain Read The Tip

Very nice and simple


Friday, July 12, 2013 - 11:44:49 AM - Neil Read The Tip

Wow, this was a lot better explanation than Microsoft's page!

Thanks a bunch, now eveyone at work loves me!


Thursday, August 01, 2013 - 6:39:46 AM - Murlidhar Read The Tip

I am using this SQL Query to import XML data to SQL

INSERT INTO tbl_XMLFile(Business,XMLFileName,XMLData, LoadedDateTime)
SELECT 'IB', 'IB_WebAppAllSitesAndUsers.xml',CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\SharePoint_Reporting\SharePoint_XML\FromLicia\AllSitesAndUsers_30July2013\IB_WebAppAllSitesAndUsers.xml', SINGLE_BLOB) AS x;

but my XML include some special character like

&#xD;&#xA;

because of this i am getting this below mentioned error

XML parsing: line 164852, character 213, illegal xml character

please guide me what to do.

 

 


Monday, September 30, 2013 - 1:06:49 PM - bub Read The Tip

Very nice and simple indeed!

I don't manage to process XML's with tags like

<cbc:IdentificationCode>BE</cbc:IdentificationCode>

If I would use

WITH IdentificationCode [varchar] (100) '@cbc:IdentificationCode'

I get "XML Parsing error : Reference to undeclared namespace prefix: 'cbc'

Any hint/tip would be greatly appreciated!

Thanks for the code!

bub

 

 


Thursday, October 10, 2013 - 7:22:42 AM - Bashir I. Mukaddam Read The Tip

Dear Arshad,

I am looking for VBScript code to open an XML file and use SQL statements like SELECT to retrieve data from XML file.

Any suggestions?

Regards,

Bashir I. Mukaddam, Dhahran, Saudi Arabia

mukaddbi@gmail.com


Tuesday, October 22, 2013 - 5:46:32 AM - kevin Read The Tip

good article.

if i need modify the data in the xml column,can you give some example,because i plan to develop a sql lib to other user which will allow them to retrieve the xml node and attribute data and modify it earsily.

i had google a lot and found their almost no example as your spread helpful.

thanks


Wednesday, October 23, 2013 - 7:29:14 PM - Arshad Read The Tip

Hi Kevin,

Please refer this tip, it might be helpful for you:

http://www.mssqltips.com/sqlservertip/2738/examples-of-using-xquery-to-update-xml-data-in-sql-server/

 


Thursday, December 19, 2013 - 5:18:55 AM - Bothwell Read The Tip

Arshad,

Thanks for the well explained article


Thursday, December 26, 2013 - 11:14:54 PM - NJ Read The Tip

Thank you Arshad, I don't usually comment on these things, but your explanation was awesome!  Your examples were easy to comprehend and adapt to my needs.  Fabulous job.


Tuesday, January 21, 2014 - 11:23:56 PM - audadi Read The Tip

Nice!!

Simple&clear..Thanks arshad


Friday, January 24, 2014 - 2:59:46 AM - Anupam Santra Read The Tip

 

I'm unbale to featch the data from xml file into relational table , kindly help me.Given below my XMl File and query.

My Table Desc.

AppId MobileNo PickupId CirSpoke DistId InsDt InsBy DocType
1 9051966812 123 KOLDST 2 1-Feb-12 VFKOL\animesh_kundu 19
2 9051965635 123 KOLDST 2 1-Feb-12 VFKOL\animesh_kundu 19
3 9051965245 123 KOLDST 2 1-Feb-12 VFKOL\animesh_kundu 19

 

My XML File.

<Appi>

  <Appi>

    <AppId>1</AppId>

    <MobileNo>9051966812</MobileNo>

    <PickupId>123</PickupId>

    <CirSpoke>KOLDST</CirSpoke>

    <DistId>2</DistId>

    <InsDt>2012-02-01T19:33:12.980</InsDt>

    <InsBy>VFKOL\animesh_kundu</InsBy>

    <DocType>19</DocType>

  </Appi>

  <Appi>

    <AppId>2</AppId>

    <MobileNo>9051965635</MobileNo>

    <PickupId>123</PickupId>

    <CirSpoke>KOLDST</CirSpoke>

    <DistId>2</DistId>

    <InsDt>2012-02-01T19:34:37.257</InsDt>

    <InsBy>VFKOL\animesh_kundu</InsBy>

    <DocType>19</DocType>

  </Appi>

  <Appi>

 

My Result is coming blnk with given query.

Declare @xml as XML,@adhoc as int,@sql NVARCHAR(Max)

 

select @xml=xmlfld from XMLtab 

EXEC sp_xml_preparedocument @adhoc output,@xml

 

select appid,MobileNo from openxml(@adhoc,'APPI/Appid')

WITH

(appid int '@appid',

mobileno varchar(10) '@mobileno')

 

EXEC sp_xml_removedocument @adhoc

GO

 

 


Tuesday, February 18, 2014 - 4:41:29 AM - Pratik Prakash Read The Tip

Excellent description Mr. Arshad, codes are working superbly.

We have recently decided to import data in the form of XML, and your reference is perfectly matching our requirement.


Friday, March 07, 2014 - 7:28:10 AM - raj Read The Tip

Hi, Nice post but i have one doubt in above example if my 'ROOT' contains namespace how to include that in stored procedure. while am using above code with namespace jus a name of table oly showing but the values not inserting. looking for some suggestions

 

Thanks in advance

 

 


Friday, March 21, 2014 - 1:00:25 PM - Meenakshi Read The Tip

Thanks Arshad, Very nice article. 

 

I am using the same concept for loading my XML to Table. Everything is fine except that when my node doesnt have full heirarchy underneath that, records are not getting loaded to the table. e.g I have below heirarchy.

so in this example highlighted bold values are going in the table but the last line that does'nt have grade2 and grade3 information is not going in the table. Can you please suggest what is wrong? I am using exact code you have explained above. Thanks for your help.

Regards Meenakshi 

<grade1 name="abc" subname="xyz">

     <grade2 name="123" subname="456">

              <grade3 name="dfg" subname="897">

               </grade3>

      </grade2>

</grade1>

<grade1 name="abc1" subname="xyz1"/>

 


Friday, April 04, 2014 - 2:03:47 PM - Oscar Read The Tip

Hi Arshad,

 

Thanks a lot for this great article.
I just have one question since I'm new on this.

Why you use "@" in the Customer attributes?
You remarked this in your post, but since I'm new on this, I don't understand how it changes the result.

 

Thanks and regards,

Oscar 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.