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

written by Diogo Souza February 25, 2022 10 comments

Problem

XML is a data format that shares data in a form that can be easily used and shared. Often, we need to import XML files into SQL Server, and there are several ways to do so. In this tip, we will look at a simple way to do this using T-SQL commands.

Solution

Many possible ways exist to perform this type of import. This tip demonstrates how to use T-SQL and OPENROWSET to read the XML data and load it into a SQL Server table. 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 a sample of XML data. You can use this as is or modify it for your tests. I copied this data and stored it 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

Next, we need to make SQL Server read the XML file, then import it using the OPENROWSET function. This function is native to T-SQL. It 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:\temp\MSSQLTIPS_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. We are querying each element of the Customer node.
The FROM clause comes from 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 elements to get all of the Customer objects properly encapsulated.

Step 4 – Check the Imported XML Data

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

SELECT * FROM CUSTOMERS_TABLE

Next Steps

Check out some other related tips:

Resources

Community

Subscribe