How to import XML data into SQL Server using SELECT query?

There are several ways available in SQL Server to import XML data like using bulk insert, SSIS (SQL Server Integration Services), using SQLCLR (SQL Common Language Runtime), using OPENXML function, etc… Here we will see a simple method to import the data in XML format by using the OPENROWSET function in a Transact-SQL SELECT and INSERT statement.

1. Our sample XML date

For our illustration, let us use the sample xml data file similar to Microsoft Docs and name it as books.xml.

<?xml version="1.0"?>
   <book id="bk101">
      <author>Smith, John</author>
      <title>Advanced SQL Techniques</title>
      <description>A comprehensive guide to advanced SQL techniques and best practices.</description>
   <book id="bk102">
      <author>Doe, Jane</author>
      <title>Data Science Essentials</title>
      <description>An essential guide for understanding data science concepts and methodologies.</description>
   <book id="bk103">
      <author>Williams, Michael</author>
      <title>Machine Learning Basics</title>
      <description>A beginner-friendly introduction to machine learning fundamentals.</description>
   <book id="bk104">
      <author>Johnson, Sarah</author>
      <title>Web Development Fundamentals</title>
      <description>A comprehensive guide to fundamental concepts in web development.</description>
   <book id="bk105">
      <author>Clark, David</author>
      <title>Data Analysis Techniques</title>
      <description>Explore various techniques and methodologies for data analysis.</description>
   <book id="bk106">
      <author>Lee, Michelle</author>
      <title>Introduction to Artificial Intelligence</title>
      <description>An introductory guide to artificial intelligence concepts and applications.</description>
   <book id="bk107">
      <author>Wilson, Robert</author>
      <title>Networking Essentials</title>
      <description>An essential reference for understanding networking fundamentals.</description>
   <book id="bk108">
      <author>Anderson, Emily</author>
      <title>Database Management Basics</title>
      <description>A beginner's guide to database management fundamentals.</description>
   <book id="bk109">
      <author>Miller, Andrew</author>
      <title>Cloud Computing Essentials</title>
      <description>An introduction to cloud computing concepts and services.</description>
   <book id="bk110">
      <author>Thomas, Elizabeth</author>
      <title>Software Development Best Practices</title>
      <description>A guide to industry-standard best practices in software development.</description>

2. Create a table to store the data

Now let’s create a table based on our sample date. let the table name be “books” and the columns we need are:

  1. book id (Primary key)
  2. author
  3. title
  4. genre
  5. price
  6. published date
  7. Description

Here is the table creation script:

USE [MyTecBitsTestDB]

CREATE TABLE [dbo].[Books](
	[Book_ID] [nvarchar](10) NOT NULL,
	[Author] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	[Genre] [nvarchar](25) NULL,
	[Price] [numeric](12, 2) NULL,
	[Published_Date] [smalldatetime] NULL,
    [Description] [nvarchar](MAX) NULL

3. Query to import the data from the XML into the SQL Server table

Now let us write a query with a SELECT statement to fetch the data from the books.xml file and then use an INSERT statement to insert the date to our Books table.

In the SELECT statement we will use the OPENROWSET function to import the values from the XML file.

	(Book_ID, Author, Title, Genre, Price, Published_Date, Description)
SELECT'@id', 'NVARCHAR(10)'),'author').value('.', 'NVARCHAR(50)'),'title').value('.', 'NVARCHAR(50)'),'genre').value('.', 'NVARCHAR(25)'),'price').value('.', 'numeric(12,2)'),'publish_date').value('.', 'smalldatetime'),'description').value('.', 'NVARCHAR(MAX)')
	FROM OPENROWSET(BULK 'E:\Beaulin\Books.xml', SINGLE_BLOB) AS T(Books_XML)) AS T(Books_XML)
      CROSS APPLY Books_XML.nodes('catalog/book') AS Books_XML (book);

4. Checking the data stored in the table

Now use a simple SELECT statement against the table Books to check the imported data.

Import XML data into SQL Server


Related Articles

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.