XML Data Type In SQL Server

Article

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 that was unsuitable and tedious in many situations. So modern database engines came along to store the XML data along with the relational data type.

SQL Server 2005 started to store the XML data using xml data type. Xml data type can store either a complete XML document, or a fragment of XML. Before xml date type the xml data was stored in varchar or text data type, that was proven to be poor in terms of querying and manipulating the XML data. In this article we will read about xml data type and also read advantages and limitations of xml data types over the 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 integer type and second column(Employee_Data) is xml type. We will store the details of employee as xml formatted in Employee_Data column.

Insert Data into table

Let us suppose that we are retrieving data of employees in xml format. Each xml record contain a unique id and 3 fragments (name, city and salary). Now we insert this xml data into 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> '
);

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

Let us check Employee_Details table.

table

We can see that Employee_Details table contain information of 5 employees.

XML data type Methods:

SQL Server provide the following 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 a xml record exist or not. Return 1 if exist else return 0.
Modify()	Updates XML data in an XML data type.
Nodes()	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 basically require an XPath expression in the XQuery parameter. If we use '/employee/name[1]' for XPath then it specify that we want to navigate to first name of employee. The ‘student/name[1]/subject[2]’ for XPath means we want to navigate the second subject of first name of student node.

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

Example:

SELECT ed.Employee_Data.query('/employee/name[1]')ASEmployee_Name,
ed.Employee_Data.query('/employee/city[1]')ASEmployee_City,
ed.Employee_Data.query('/employee/salary[1]')ASEmployee_Salary
FROM dbo.Employee_Detailsed;

Output:

output

Example:

SELECT ed.Employee_Data.query('/employee/name[1]/text()')ASEmployee_Name,
ed.Employee_Data.query('/employee/city[1]/text()')ASEmployee_City,
ed.Employee_Data.query('/employee/salary[1]/text()')ASEmployee_Salary
FROM dbo.Employee_Detailsed;

Output:

output

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

The value() Method:

The value() method is used to retrieve a value of SQL type from an XML instance. The value() method works similar as a combination of query() method with text() method, except that value() method allows us to define the data type. If you don’t require specifying the data type then 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 query() method.

Example:

SELECTed.Employee_Data.value('(/employee/name)[1]','nvarchar(max)')ASEmployee_Name,
ed.Employee_Data.value('(/employee/city)[1]','nvarchar(max)')ASEmployee_City,
ed.Employee_Data.value('(/employee/salary)[1]','int')ASEmployee_Salary
FROMdbo.Employee_Detailsed;

Output:

value

The exist() Method:

The exist() method is used to check if the specified XPath exists or not.

Example:

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

Output:

exist

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

Example:

SELECTed.Employee_Data.exist('/employee/name[2]')ASEmployee_Exist
FROMdbo.Employee_Detailsed
WHEREed.Id=3;

Output:

In the above example exist method return false(0) because employee only contains a single name attribute for each instance, so it doesn’t find 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 xml stream. The modify() method take the XPath as 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 value of id is 1(Employee_Details.Id=1).

Let us check the value Employee_Details table again.

table

We can see that salary for “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 above we insert a new employee record for id 2, let us check this new inserted record.

record

Example:

UPDATEEmployee_Details
SETEmployee_Data.modify('delete (/employee)[2]');

Output:

output

In above example we delete the second employee record from each row.

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 result of the nodes() method contain a rowset that has 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.

nodes

In the above table we can see that 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
CROSSAPPLY Employee_Data.nodes('(/employee)')AS MyNodes(Employee_Data)

Output:

output

When to use XML data type over Relational data type

If data structure is semi-structured or unstructured, or unknown.
When you want to create platform-independent model.
If data represents containment hierarchy or nested hierarchy.
If you want to query into the data or update parts of it, based on its structure.

When to use Relational data type

If data is structured or known.
If none of the xml use condition met.
If application contain xml data but we 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 cast or convert to text or ntext.
Column containing XML data type can’t be used as index.
XML data type can’t be used as parameters for built in function 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 requirement of the application we should choose the appropriate data type b/w the relational and xml data type.