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"?>
<catalog>
<book id="bk101">
<author>Smith, John</author>
<title>Advanced SQL Techniques</title>
<genre>Computer</genre>
<price>59.95</price>
<publish_date>2003-05-10</publish_date>
<description>A comprehensive guide to advanced SQL techniques and best practices.</description>
</book>
<book id="bk102">
<author>Doe, Jane</author>
<title>Data Science Essentials</title>
<genre>Technology</genre>
<price>39.99</price>
<publish_date>2018-09-20</publish_date>
<description>An essential guide for understanding data science concepts and methodologies.</description>
</book>
<book id="bk103">
<author>Williams, Michael</author>
<title>Machine Learning Basics</title>
<genre>Technology</genre>
<price>49.95</price>
<publish_date>2016-07-15</publish_date>
<description>A beginner-friendly introduction to machine learning fundamentals.</description>
</book>
<book id="bk104">
<author>Johnson, Sarah</author>
<title>Web Development Fundamentals</title>
<genre>Computer</genre>
<price>29.99</price>
<publish_date>2019-03-30</publish_date>
<description>A comprehensive guide to fundamental concepts in web development.</description>
</book>
<book id="bk105">
<author>Clark, David</author>
<title>Data Analysis Techniques</title>
<genre>Technology</genre>
<price>44.95</price>
<publish_date>2017-11-05</publish_date>
<description>Explore various techniques and methodologies for data analysis.</description>
</book>
<book id="bk106">
<author>Lee, Michelle</author>
<title>Introduction to Artificial Intelligence</title>
<genre>Technology</genre>
<price>49.99</price>
<publish_date>2020-02-12</publish_date>
<description>An introductory guide to artificial intelligence concepts and applications.</description>
</book>
<book id="bk107">
<author>Wilson, Robert</author>
<title>Networking Essentials</title>
<genre>Technology</genre>
<price>39.95</price>
<publish_date>2015-06-25</publish_date>
<description>An essential reference for understanding networking fundamentals.</description>
</book>
<book id="bk108">
<author>Anderson, Emily</author>
<title>Database Management Basics</title>
<genre>Technology</genre>
<price>34.95</price>
<publish_date>2014-08-18</publish_date>
<description>A beginner's guide to database management fundamentals.</description>
</book>
<book id="bk109">
<author>Miller, Andrew</author>
<title>Cloud Computing Essentials</title>
<genre>Technology</genre>
<price>49.95</price>
<publish_date>2018-12-10</publish_date>
<description>An introduction to cloud computing concepts and services.</description>
</book>
<book id="bk110">
<author>Thomas, Elizabeth</author>
<title>Software Development Best Practices</title>
<genre>Computer</genre>
<price>54.99</price>
<publish_date>2016-04-20</publish_date>
<description>A guide to industry-standard best practices in software development.</description>
</book>
</catalog>
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:
- book id (Primary key)
- author
- title
- genre
- price
- published date
- Description
Here is the table creation script:
USE [MyTecBitsTestDB]
GO
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
CONSTRAINT [PK_Books] PRIMARY KEY ( [Book_ID] )
)
GO
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.
INSERT INTO Books
(Book_ID, Author, Title, Genre, Price, Published_Date, Description)
SELECT
Books_XML.book.value('@id', 'NVARCHAR(10)'),
Books_XML.book.query('author').value('.', 'NVARCHAR(50)'),
Books_XML.book.query('title').value('.', 'NVARCHAR(50)'),
Books_XML.book.query('genre').value('.', 'NVARCHAR(25)'),
Books_XML.book.query('price').value('.', 'numeric(12,2)'),
Books_XML.book.query('publish_date').value('.', 'smalldatetime'),
Books_XML.book.query('description').value('.', 'NVARCHAR(MAX)')
FROM (SELECT CAST(Books_XML AS xml)
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.
SELECT * FROM Books
Reference
- More about OPENROWSET at Microsoft Docs.