Year End Sale: Get Upto 40% OFF on Live Training! Offer Ending in
D
H
M
S
Get Now
SQL Server XML Data Type

SQL Server XML Data Type

30 Jul 2024
Intermediate
139K Views
16 min read
Learn with an interactive course and practical hands-on labs

SQL Server Course

SQL Server XML Data Type: An Overview

SQL Server's XML data type allows for the storage and manipulation of XML data within the database, making it easier to query, change, and transform XML documents directly within SQL queries. Consider taking a SQL Server Tutorial or enrolling in a SQL Server Certification Course to gain a better understanding and mastery of XML processing in SQL Server.

XML Data Type in SQL Server

XML data type was introduced in SQL Server 2005 to work with XML data. Using this data type, we can store XML in its native format and can also query/modify the XML data within the xml. We can use xml data types like:
  1. Variable
  2. Field/Column in a table
  3. Parameter in the user-defined function (UDF) or stored procedure(SP)
  4. Return value from a UDF or SP

We can define xml data type field to NOT NULL or we can provide a default value to it.

Read More - Top DBMS Interview Questions and Answers

Different XML Data Types Methods

SQL Server supports five XML data type methods for extracting and manipulating XML data.

  1. Query()
  2. Value()
  3. Exist()
  4. Modify()
  5. Nodes()

1. Query() Method

The query() method allows you to connect XPath expressions to an XML instance stored in an XML data type column. It treats XML data like a tiny database, allowing you to query specific components or characteristics using XPath syntax.

Example

DECLARE @xml xml = N'<bookstore>
 <book genre="fantasy">
  <title>The Lord of the Rings</title>
 </book>
 <book genre="sci-fi">
  <title>Ender\'s Game</title>
 </book>
</bookstore>';

SELECT b.value('@genre', 'nvarchar(50)') AS Genre, b.value('(title/text())[1]', 'nvarchar(max)') AS Title
FROM @xml.query('//book') AS b;

This example uses XPath expressions in the query() function to obtain the genre attribute and text content of the first <title> element within each <book> element.

2. Value() Method

The value() method returns a single value of a certain data type from an XML object. It accepts two arguments: the XPath expression for locating the element or attribute, and the data type for the returned value.

Example

DECLARE @xml xml = N'<order id="123">
 <customer name="John Doe" />
 <items>
  <item name="Book" price="19.99" />
 </items>
</order>';

SELECT @xml.value('//customer/@name', 'nvarchar(50)') AS CustomerName,
   @xml.value('//items/item/@price', 'decimal(5,2)') AS ItemPrice;

This example uses value() to extract the name property from the <customer> element and the price attribute from the first <item> element.

3. Exist() Method

The exist() method determines whether an XPath expression returns any nodes within the XML instance. It returns 1 if nodes are detected, and 0 otherwise.

Example

DECLARE @xml xml = N'<document>
 <section>Data Analysis</section>
 <section>Machine Learning</section>
</document>';

SELECT CASE WHEN @xml.exist('//section[@name="Database"]') = 1 THEN 'Section exists' ELSE 'Section does not exist' END AS Result;

This example uses exist() to check if the XML document contains a section> element with the name property set to "Database".

4. Modify() Method

The modify() method lets you change the structure and content of XML data using XML Data Modification Language (XML DML) commands. These statements encompass insert, update, remove, and replace operations for specified items or attributes.

Example

DECLARE @xml xml = N'<product id="100">
 <name>T-Shirt</name>
 <color>Red</color>
</product>';

SELECT @xml.modify('insert <size>Large</size> after /product/color');

In this example, alter() is used to place a new <size> element with the text "Large" after the < color> element within the <product> element.

5. Nodes() Method

The nodes() method "shreds" XML data into a relational database structure. It treats each element or property in the XPath query as a distinct row. This makes it easier to work with XML data using conventional T-SQL queries.

Example

DECLARE @xml xml = N'<bookstore>
 <book genre="fantasy">
  <title>The Hobbit</title>
 </book>
 <book genre="sci-fi">
  <title>Dune</title>
 </book>
</bookstore>';

SELECT * FROM @xml.nodes('//book') AS b(Genre nvarchar(50), Title nvarchar(max));

This example uses nodes() to generate a result set with two columns, "Genre" and "Title," for each <book> element in the XML data.

Advantages of using XML data type in SQL Server

  • XML is used to store unstructured data in a relational database.
  • XML itself checks the information inside the file with tags and their values using DTD (Document Type Definition) and schema.
  • XML data type holds independent data structures, therefore it can be readily merged or used in other database sources.
  • In a way, the XML data type decreases the back-end application burden because XML is easily used with a UI.
  • XML data type can be used with the input argument in a function or stored procedure.

Disadvantages of using XML data with SQL Server

  • In SQL Server data pages, the XML data type takes up more space than the relational (row/column) format since it is redundant with the XML tag-value set.
  • In comparison to a normalized structure, query execution, and data manipulation may take longer.
  • Large XML documents require additional server resources, including CPU, memory, and IO, in SQL Server.
  • Additionally, the XML query format is complex.

Limitation Of XML Data type

  • We can’t directly compare an instance of the XML data type to another instance of the XML data type. For equality comparisons, we first need to convert the XML type to a character type.
  • We can’t use GROUP BY or ORDER BY with an XML data type column.
  • We can’t use XML data type field as a primary key, Unique key, or foreign key.
  • We can’t define the XML data type field with the COLLATE keyword.

Query XML Data

Suppose we have the following tables in the database. Using these tables we will produce query results as an xml

CREATE TABLE Department (
 DeptID int IDENTITY(1,1) primary key ,
 DeptName varchar(50) NULL,
 Location varchar(50) NULL )
CREATE TABLE Employee (
 EmpID int IDENTITY(1,1) NOT NULL,
 EmpName varchar(50) NULL,
 Address varchar(100) NULL,
 DeptID int foreign Key references Department(DeptID) 
 )
--Now Insert data into these tables
INSERT INTO Department (DeptName,Location)VALUES('HR','Delhi')
INSERT INTO Department (DeptName,Location)VALUES('IT','Delhi')
INSERT INTO Department (DeptName,Location)VALUES('Technical','Delhi')
INSERT INTO Employee( EmpName,Address,DeptID)VALUES('Shailendra','Noida',2)
INSERT INTO Employee( EmpName,Address,DeptID)VALUES('Mohan','Noida',2)
INSERT INTO Employee( EmpName,Address,DeptID)VALUES('Vipul','Noida',1)
INSERT INTO Employee( EmpName,Address,DeptID)VALUES('Mrinal','Noida',3) 

We can retrieve data table records as xml data using the FOR XML clause in the SELECT statement. In the FOR XML Clause, we can define three xml modes.

AUTO

It generates output with both element and attribute features in combination with a sub-query.

SELECT DeptName, EmpID
FROM Employee AS Emp JOIN Department AS Dept
ON Emp.DeptID= Dept.DeptID
FOR XML AUTO; 
This SQL query retrieves the Department Name and Employee ID from the Employee table, joins them with the Department table using matching Department IDs, then formats the output as XML with nested components for each entry.
Output:
 <Dept DeptName="IT">
 <Emp EmpID="1" />
 <Emp EmpID="2" />
</Dept>
<Dept DeptName="HR">
 <Emp EmpID="3" />
</Dept>
<Dept DeptName="Technical">
 <Emp EmpID="4" />
 <Emp EmpID="5" />
</Dept> 

EXPLICIT

It converts the rowset which is the result of the query execution, into an XML document. This mode provides more control over the format of the XML, which means in which format you want xml you need to define that format in a select query.

SELECT
 1 tag,
 NULL parent,
 EmpID [employee!1!ID],
 EmpName [employee!1!name],
 NULL [order!2!date],
 NULL [department!3!name]
FROM Employee
UNION ALL 
SELECT
 3,
 1,
 EmpID,
 NULL,
 NULL,
 DeptName
FROM Employee e JOIN Department d
ON e.DeptID=d.DeptID
ORDER BY 3, 1
FOR XML EXPLICIT; 
This SQL query collects employee and department data, assigns tags and parent relationships for XML production, sorts by employee ID, and formats as XML with explicit structure.
Output
<employee ID="1" name="Shailendra">
 <department name="IT" />
</employee>
<employee ID="2" name="Mohan">
 <department name="IT" />
</employee>
<employee ID="3" name="Vipul">
 <department name="HR" />
</employee>
<employee ID="4" name="Mrinal">
 <department name="Technical" />
</employee>
<employee ID="5" name="Jitendra">
 <department name="Technical" />
</employee> 

RAW

It produces a single element the optionally provided element name for each row in the query result set that is returned by a select statement.

SELECT Emp.EmpID, Dept.DeptName
Employee as Emp JOIN Department as Dept
ON Emp.DeptID= Dept.DeptID
FOR XML RAW; 
This SQL query collects Employee IDs and corresponding Department Names by connecting the Employee and Department tables based on matching Department IDs, and then prepares the output as XML using a simple row-based structure in RAW mode.
Output
<row EmpID="1" DeptName="IT" />
<row EmpID="2" DeptName="IT" />
<row EmpID="3" DeptName="HR" />
<row EmpID="4" DeptName="Technical" />
<row EmpID="5" DeptName="Technical" /> 

Read More

Summary

In this article, I try to explain the XML Data Type with examples. I hope after reading this article you will be aware of XML Datatype in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

FAQs

The SQL Server XML data type enables for the storage and manipulation of XML data within the database, making it easier to store and retrieve structured content.

You can store XML documents directly in SQL Server by defining a column with the XML data type or using XML data type variables.

Yes, SQL Server has built-in support for querying XML data using XPath or XQuery expressions to get specific items or attributes.

SQL Server's XML data can be updated by calling methods such as .modify() to add, delete, or edit components or attributes within XML documents.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan, Founder and CEO of ScholarHat by DotNetTricks, is a renowned expert in System Design, Software Architecture, Azure Cloud, .NET, Angular, React, Node.js, Microservices, DevOps, and Cross-Platform Mobile App Development. His skill set extends into emerging fields like Data Science, Python, Azure AI/ML, and Generative AI, making him a well-rounded expert who bridges traditional development frameworks with cutting-edge advancements. Recognized as a Microsoft Most Valuable Professional (MVP) for an impressive 9 consecutive years (2016–2024), he has consistently demonstrated excellence in delivering impactful solutions and inspiring learners.

Shailendra’s unique, hands-on training programs and bestselling books have empowered thousands of professionals to excel in their careers and crack tough interviews. A visionary leader, he continues to revolutionize technology education with his innovative approach.
Accept cookies & close this