SQL Server XML Query

XML or eXtensible Markup Language is a markup language for transport and storing data. XML is simple and very descriptive in its nature. This makes it a very common language for sharing information between platforms and applications.

In this guide, you will learn how to use XML data in SQL Server. We will discuss creating XML information from existing tables, loading XML documents, and more.

Getting Started

To better understand how to use XML data in SQL Server, we will first create sample data as shown in the following queries:

-- create test database
createdatabase users;
-- switch db;
use users;
createtableuser_info (
id intidentity(1,1) notnullprimary key,
namevarchar(125),
email varchar(255),
department varchar(50),
salary money
);
insertintouser_info(name, email, department, salary)
values ('Brandon Benitez', '[email protected]', 'Game Developer', $140000),
('Paulina Bowman', '[email protected]', 'Fullstack Developer', $133000),
('Carl Crawford', '[email protected]', 'Frontend Developer', $110000),
('Brian Booth', '[email protected]', 'Database Developer', $150000),
('Jasmin Pham', '[email protected]', 'WordPress Developer', $102000),
('Tamara Davidson', '[email protected]', 'Graphics Developer', $122000),
('Jaylan Ho', '[email protected]', 'DevOps Developer', $111000),
('Winfred Fleming', 'f_[email protected]', 'Backend Developer', $145000);

Once we have the sample data, we can proceed to learn how to work with XML in SQL Server.

Convert SQL Table to XML

We can convert data from an SQL table into XML using the FOR XML AUTO and FOR XML PATH statements.

For example, to convert the previous sample table into XML, we can run the query as:

select * from user_info

for xml auto;

The above query will return an XML string as shown:

In SQL Server Management Studio, you can click the XML link to show and format the XML data in a readable format as:

Notice the XML contains 5 user entities with each column of the table as the attribute and its associated value.

You can also use the FOR XML PATH to convert a table into XML. This option converts each record in the table as an element and columns as nested elements.

An example is as shown:

select * from user_info

for xml path

The query should return the XML data as shown:

Here, each record in the table is represented as an element with the columns and values nested in the main element.

To change the root node from a row to a custom name, we can do the following:

select * from user_info

for xml path

Load XML File to SQL

We can load XML data from a file using the openrowset statement. Consider the following example:

createtableuser_xml(
xmldataxml
);
insertintouser_xml(xmldata)
selectconvert(xml, BulkColumn) asBulkColumn
fromopenrowset(bulk'C:\users\cs\Documents\user_info.xml', single_blob) ascorrelation_name;
select * fromuser_xml;

The query should import the specified XML file into the created table.

Convert XML to Table

To convert data from an XML file to a table, we can use the sp_xml_preparedocument and openxml function.

-- convert xml to table
declare @user_info xml
select @user_info = info
fromopenrowset (bulk 'C:\users\cs\Documents\user_info.xml', single_blob) asuser_info(info)
select @user_info
declare @hdoc int

exec sp_xml_preparedocument @hdoc output, @info_info
select *
fromopenxml(@hdoc, '/user_info/user_info', 2)
with(
namevarchar(125),
email varchar(255),
department varchar(50),
salary money
)
exec sp_xml_removedocument @hdoc

The query should parse the XML file and convert the attributes to a table.

Conclusion

This guide covers the basics of working with XML data in the SQL Server, creating XML information from existing tables, and loading the XML documents. We hope you found this article helpful. Check out more Linux Hint articles for more tips and SQL server information.