XML Data Type In SQL Server

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

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

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;    

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;  

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#