XML Data Type In SQL Server

Introduction

XML is used everywhere in business to coordinate, collaborate, and exchange data with other businesses. In the classic approach, the XML data was stored in a physical disk which was unsuitable and tedious in many situations. So modern database engines came to store XML and relational data types.

XML Data Type in SQL Server

SQL Server started to store the XML data using the XML data type. The XML data type can store either a complete XML document or a fragment of XML. Before XML data type, the XML data was stored in varchar or text data type, which was proven to be poor in querying and manipulating the XML data. In this article, we will read about the XML data type and the advantages and limitations of XML data types over relational data types.

Create a table with XML data type

CREATE TABLE Employee_Details    
(    
        Id int PRIMARYKEY,    
        Employee_Data XML NOTNULL    
); 

In the above query, we created an Employee_Details table, first column(id) of this table is an integer type and the second column(Employee_Data) is an XML type. We will store the details of employees as XML formatted in the Employee_Data column.

Insert Data into the table

Let us suppose that we are retrieving the data of employees in XML format. Each XML record contains a unique id and three fragments (name, city, and salary). Now we insert this XML data into the Employee_Details table.

INSERT INTO dbo.Employee_Details    
(    
    Id,    
    Employee_Data    
)    
VALUES    
(    
    1,    
    '<employee empId="1"> < name > Pankaj Choudhay < /name> < city > Alwar < /city> < salary > 21000 < /salary> < /employee> '
);   

XML record holds the employee's name, city, and salary information. In the above query, we inserted the record of employees in the table. Similar to the above query, we insert more data into the table.

Let us check the Employee_Details table.

 

 

We can see that the Employee_Details table contains information about five employees.

XML data type Methods

SQL Server provides five XML data type methods for extracting or manipulating the XML data.

Method	Description
Query()	extract XML fragments from an XML data type.
Value()	extract a single value from an XML fragment.
Exist()	Determine whether an XML record exists or not. Return one if it exists. Else, return 0.
Modify()	Updates XML data in an XML data type.
Nodes()	I am used to shred XML into multiple rows to propagate parts of XML documents into rowsets.

The query() Method

This method is used to query over an XML instance. This method requires an XPath expression in the XQuery parameter. If we use '/employee/name[1]' for XPath, then it specifies that we want to navigate to the employee's first name. The 'student/name[1]/subject[2]' for XPath means we want to navigate the second subject of the first name of the student node.

The query( ) method returns the XML fragment that contains everything between the starting and ending tags.

Example

SELECT ed.Employee_Data.query('/employee/name[1]')AS Employee_Name,      
ed.Employee_Data.query('/employee/city[1]')AS Employee_City,      
ed.Employee_Data.query('/employee/salary[1]')AS Employee_Salary      
FROM dbo.Employee_Detailsed;    

Output

 

Example

SELECT ed.Employee_Data.query('/employee/name[1]/text()')AS Employee_Name,      
ed.Employee_Data.query('/employee/city[1]/text()')AS Employee_City,      
ed.Employee_Data.query('/employee/salary[1]/text()')AS Employee_Salary      
FROM dbo.Employee_Detailsed;  

 Output

 

In the previous example, we retrieved data in XML form. In the above example, we used the text() method and can now see the data in an XML format. If we want to return data without XML format, then we can use the text() method and add the text() method at the end of Xpath in the parameter of the query() method.

The value() Method

The value() method retrieves a value of SQL type from an XML instance. The value() method works similarly to a combination of the query() method with the text() method, except that value() method allows us to define the data type. If you don't require specifying the data type, then the query() method is best in that scenario, but if you want to define some specific data type like float, numeric, or money, then you should prefer the value() method instead of the query() method.

Example 

SELECT ed.Employee_Data.value('(/employee/name)[1]','nvarchar(max)')AS Employee_Name,      
ed.Employee_Data.value('(/employee/city)[1]','nvarchar(max)')AS Employee_City,      
ed.Employee_Data.value('(/employee/salary)[1]','int')AS Employee_Salary      
FROM dbo.Employee_Detailsed; 

Output

 

The exist() Method

The exist() method is used to check whether the specified XPath exists.

Example

SELECT ed.Employee_Data.exist('/employee/name[1]')AS Employee_Exist    
FROM dbo.Employee_Detailsed    
WHERE ed.Id=3;  

Output

 

In the above example, we check whether the first instance of the name exists in the employee or not, as we know that each employee's data contains a name attribute. 

Example

SELECT ed.Employee_Data.exist('/employee/name[2]') AS Employee_Exist    
FROM dbo.Employee_Detailsed    
WHERE ed.Id=3;  

Output

 

In the above example, exist method returns false(0) because the employee only contains a single name attribute for each instance, so it doesn't find a second name attribute.

The modify() Method

The modify() method is used to specify XML DML statements to perform updates. The modify() method allows us to change the values directly in the XML stream. The modify() method takes the XPath as a parameter to which part of XML will update, and modify() method only takes a single data value at a time.

Example

UPDATE Employee_Details    
SET Employee_Data.modify('replace value of (/employee/salary/text())[1] with 45000')    
WHERE Employee_Details.Id=1;   

In the above example, we updated the value of salary for Employee for which the value of id is 1(Employee_Details.Id=1).

Let us recheck the value Employee_Details table.

 

We can see that salary for the "Pankaj Choudhary" employee has been changed to 45000.

Example

UPDATE Employee_Details     SET Employee_Data.modify(
  'insert <employee empId="6">    
<name>Priya</name>    
<city>Mathura</city>    
<salary>51000</salary>    
</employee>    
after (/employee)[1]'
)      Where Employee_Details.Id = 2;

In the above, we insert a new employee record for id 2; let us check this newly inserted record.

 

Example

UPDATE Employee_Details    
SET Employee_Data.modify('delete (/employee)[2]');    

Output

 

We delete the second employee record from each row in the above example.

The nodes() Method

The nodes() method is used to shred XML into multiple rows to propagate parts of XML documents into rowsets. It allows us to identify nodes that will be mapped into a new row. The node () method's result contains a rowset with logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression so that subsequent queries can navigate relative to these context nodes.

Let us consider the following table.

 

In the above table, we can see that the first two rows contain two employee instances. Now we will see the following.

Example

SELECT ed.Id,ed.Employee_Data.query('/employee/name[1]') AS nodes    
FROM dbo.Employee_Detailsed    
CROSS APPLY Employee_Data.nodes('(/employee)')AS MyNodes(Employee_Data)  

Output

 

When to use XML data type over Relational data type?

• If the data structure is semi-structured or unstructured, or unknown.

• When you want to create a platform-independent model.

• If data represents containment hierarchy or nested hierarchy.

• If you want to query the data or update parts of it based on its structure.

When to use the Relational data type?

• If data is structured or known.

• If none of the XML use conditions are met.

• If the application contains XML data, we are only required to retrieve and store the data. Instead, xmlrelational data should be used.

Limitations of XML data type

• XML data can't be sorted.

• XML data storage can't exceed the limitation of 2GB storage space.

• XML data can't be cast or converted to text or ntext.

• The column containing XML data type can't be used as an index.

• XML data type can't be used as parameters for built-in functions other than ISNULL, COALESCE, and DATALENGTH.

Conclusion

SQL Server provides XML data type to store, update, and retrieve the XML formatted data. As per the application requirement, we should choose the appropriate data type b/w the relational and XML data type.

Read more articles on SQL Server.

• Get SQL Server Table Records in XML

• Easy SQL Server Tool Using C#