Simple way to Import XML Data into SQL Server with T-SQL

By:   |   Updated: 2022-02-25   |   Comments (10)   |   Related: More > XML

Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don't necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE <<

Thank you,
Greg Robidoux and Jeremy Kadlec ( Co-Founders)

XML is a data format used to share data in a form that can be easily used and shared. There is often the need import XML files into SQL Server which can be done several ways and in this tip we will look at a simple way to do this using just T-SQL commands.


There many possible ways to perform this type of import and in this tip we will show how this can be done using T-SQL and OPENROWSET to read the XML data and load into a SQL Server table.  In order for you to understand it better, let’s walk through an example.

Step 1 – Create table to store imported data

Let’s create a simple table thatíll store the data of our customers.

USE mssqltips_db

    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DOCUMENT] [varchar](20) NOT NULL,
    [NAME] [varchar](50) NOT NULL,
    [ADDRESS] [varchar](50) NOT NULL,
    [PROFESSION] [varchar](50) NOT NULL,

Step 2 - Create Sample XML File

Below is sample XML data.  You can use this as is or modify for your own tests. I copied this data and stored in a file named MSSQLTIPS_XML.xml.

<?xml version="1.0" encoding="utf-8"?>
    <Document>000 000 000</Document>
    <Name>Mary Angel</Name>
    <Address>Your City, YC 1212</Address>
    <Profession>Systems Analyst</Profession>
    <Document>000 000 001</Document>
    <Name>John Lenon</Name>
    <Address>Your City, YC 1212</Address>
    <Document>000 000 002</Document>
    <Name>Alice Freeman</Name>
    <Address>Your City, YC 1212</Address>
    <Document>000 000 003</Document>
    <Name>George Sands</Name>
    <Address>Your City, YC 1212</Address>
    <Document>000 000 004</Document>
    <Name>Mark Oliver</Name>
    <Address>Your City, YC 1212</Address>

Step 3 – Importing the XML data file into a SQL Server Table

Now all we need is to make SQL Server read the XML file and import the data via the OPENROWSET function. This function is native to T-SQL and allows us to read data from many different file types through the BULK import feature, which allows the import from lots of file types, like XML.

Here is the code to read the XML file and to INSERT the data into a table.

   MY_XML.Customer.query('Document').value('.', 'VARCHAR(20)'),
   MY_XML.Customer.query('Name').value('.', 'VARCHAR(50)'),
   MY_XML.Customer.query('Address').value('.', 'VARCHAR(50)'),
   MY_XML.Customer.query('Profession').value('.', 'VARCHAR(50)')
      CROSS APPLY MY_XML.nodes('Customers/Customer') AS MY_XML (Customer);
  • The first thing we are doing is a simple INSERT into our table CUSTOMERS_TABLE.
  • The columns in the SELECT are pulled from the alias we created named MY_XML and we are querying each element of the Customer node.
  • The FROM clause is derived by using the OPENROWSET operation using the BULK option and the SINGLE_BLOB option to have the data returned from the XML file into a single column and row. The function nodes() along with CROSS APPLY allows navigation through the XML element’s in order to get all of Customer objects properly encapsulated.

Step 4 - Check the Imported XML Data

After the insert, you can query the table to check the results.

xml file import query results
Next Steps

Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights

get scripts

next tip button

About the author
MSSQLTips author Diogo Souza Diogo Souza has been passionate about clean code, data manipulation, software design and development for almost ten years.

View all my tips

Article Last Updated: 2022-02-25

Comments For This Article

Thursday, March 17, 2022 - 9:32:24 AM - Greg Robidoux Back To Top (89895)
Hi Marcin,

I don't think that is possible. If I find something that says it can be done I will add another comment.


Thursday, March 17, 2022 - 7:54:18 AM - Marcin Bednarek Back To Top (89893)
is it possible to import XML data directly from URL ? or I've to download if to disk first ?

Thursday, May 6, 2021 - 1:38:44 AM - Medyanik Yevhen Back To Top (88652)
What does MY_XML actually means? I don't get it.

Friday, July 10, 2020 - 1:48:38 PM - Clay Back To Top (86119)

Thank you so much for this.  It's clear, concise and works!

Thursday, March 26, 2020 - 12:37:14 PM - Chris Morse Back To Top (85196)

Very clear instructions ... Thank you.  

I'll post back and let you know if I made it work correctly.

Thursday, October 17, 2019 - 4:49:35 AM - Aamir Back To Top (82804)

How to load below XML data into SQL?


<?xml version="1.0" encoding="utf-8"?> 

<DataTable xmlns="SmarttraceWS"> 

  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> 

    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="ActivityRecords" msdata:UseCurrentLocale="true"> 


        <xs:choice minOccurs="0" maxOccurs="unbounded"> 

          <xs:element name="ActivityRecords"> 



                <xs:element name="ReferenceID" type="xs:long" minOccurs="0" /> 

                <xs:element name="IMEI" type="xs:string" minOccurs="0" /> 

                <xs:element name="Asset" type="xs:string" minOccurs="0" /> 

                <xs:element name="Driver" type="xs:string" minOccurs="0" /> 

                <xs:element name="DateTime" type="xs:string" minOccurs="0" /> 









  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"> 

    <DocumentElement xmlns=""> 

      <ActivityRecords diffgr:id="ActivityRecords1" msdata:rowOrder="0"> 





        <DateTime>2019-10-14 12:00:35</DateTime> 






Wednesday, September 11, 2019 - 4:59:49 AM - Arpan Back To Top (82353)

I get the following error

Cannot bulk load because the file "D:\MSSQLTIPS_XML.xml" could not be opened. Operating system error code 21(The device is not ready.).

Tuesday, January 8, 2019 - 11:07:44 AM - Tom Back To Top (78670)

†If the XML file contains a reference to a DTD file, you will need to change the CAST to a CONVERT to avoid the "Cannot parse..." error.† As:


Thursday, October 11, 2018 - 10:23:46 AM - Eirikur Eiriksson Back To Top (77924)

Mikael Eriksson beat me to it!

The text() function prevents the result set from being constructed as an XML, removes at least two blocking operators from the execution plan.

Wednesday, October 10, 2018 - 2:30:07 AM - Mikael Eriksson Back To Top (77911)


There are some issues with your code that makes it go really slow. Specificcly the cast to XML and the use of the query() function.†

Add a couple of hundred Customer nodes to your XML and compare duration between your version and†a rewritten version below.†

On my machine for 300 Customer elements, duration goes from 8 seconds down to 18 millisecond.

declare @X xml;
select @X = T.MY_XML
from openrowset(bulk 'D:\slask\MSSQLTIPS_XML.xml', single_blob) as T(MY_XML)
† †MY_XML.Customer.value('(Document/text())[1]', 'VARCHAR(20)'),
† †MY_XML.Customer.value('(Name/text())[1]', 'VARCHAR(50)'),
† †MY_XML.Customer.value('(Address/text())[1]', 'VARCHAR(50)'),
† †MY_XML.Customer.value('(Profession/text())[1]', 'VARCHAR(50)')
from @X.nodes('Customers/Customer') AS MY_XML (Customer);


get free sql tips
agree to terms