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



By:   |   Read Comments   |   Related Tips: More > XML







Problem

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.

Solution

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
GO

CREATE TABLE [CUSTOMERS_TABLE](
    [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,
 CONSTRAINT [CUSTOMERS_PK] PRIMARY KEY ([Id])
)
GO

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"?>
<Customers>
  <Customer>
    <Document>000 000 000</Document>
    <Name>Mary Angel</Name>
    <Address>Your City, YC 1212</Address>
    <Profession>Systems Analyst</Profession>
  </Customer>
  <Customer>
    <Document>000 000 001</Document>
    <Name>John Lenon</Name>
    <Address>Your City, YC 1212</Address>
    <Profession>Driver</Profession>
  </Customer>
  <Customer>
    <Document>000 000 002</Document>
    <Name>Alice Freeman</Name>
    <Address>Your City, YC 1212</Address>
    <Profession>Architect</Profession>
  </Customer>
  <Customer>
    <Document>000 000 003</Document>
    <Name>George Sands</Name>
    <Address>Your City, YC 1212</Address>
    <Profession>Doctor</Profession>
  </Customer>
  <Customer>
    <Document>000 000 004</Document>
    <Name>Mark Oliver</Name>
    <Address>Your City, YC 1212</Address>
    <Profession>Writer</Profession>
  </Customer>
</Customers>

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.

INSERT INTO CUSTOMERS_TABLE (DOCUMENT, NAME, ADDRESS, PROFESSION)
SELECT
   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)')
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:tempMSSQLTIPS_XML.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      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

  • Check out some other related tips:


Last Update:


next webcast button

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


Leave a Reply

Your email address will not be published. Required fields are marked *

HashFlare