SQL Server XQuery Methods

SQL Server provides XQuery methods to query xml file or xml data. Using these methods we can Insert, Update, Delete data in xml file or in XML Data Type variable. In SQL Server XQuery statements are case sensitive since xml is case sensitive. Hence while query to xml data remember this thing.

XQuery Methods

We have following xml data to implement all the XQuery methods given below.

DECLARE @xml XML

SET @xml='<EmployeeMaster>

DECLARE @xml XML

SET @xml='<EmployeeMaster>

<Employee EmpNo="1" Name="Anand">

    <EmpSub No="1" Name="Maths"></EmpSub>

    <EmpSub No="2" Name="English"></EmpSub>

    <EmpSub No="3" Name="Hindi"></EmpSub>

</Employee>

<Employee EmpNo="2" Name="Edalquvin">

    <EmpSub No="1" Name="Maths"></EmpSub>

</Employee>

<Employee EmpNo="1" Name="Anitha">

    <EmpSub No="1" Name="History"></EmpSub>

    <EmpSub No="2" Name="Tamil"></EmpSub>

    <EmpSub No="3" Name="English"></EmpSub>

</Employee>

</EmployeeMaster>';

1. xml.exist()

This method returns a boolean value depends upon the condition in this method like as

SELECT @xml.exist('/EmployeeMaster/Employee[@Name = "Anand"]') as Result1

SELECT @xml.exist('/EmployeeMaster/Employee[@Name = "Aand"]') as Result2

Output

2. xml.query()

This method takes an XQuery statement and returns an instance of the XML data type like as

SELECT @xml.query('/EmployeeMaster/Employee') as Employee

Output

SELECT @xml.query('distinct-values( data(/EmployeeMaster/Employee/EmpSub/@Name))') as Subject

Output

3. xml.value()

This method takes an XQuery statement and returns a single value after type casting like as

SELECT @xml.value('/EmployeeMaster[1]/Employee[1]/@Name',   'VARCHAR(4)') as ResultName

SELECT @xml.value('/EmployeeMaster[1]/Employee[2]/@Name', 'VARCHAR(4)') as ResultName

Output

4. xml.nodes()

This method takes an XQuery statement and returns a single value after type casting like as

SELECT x.value('@EmpNo', 'int') AS UserNo, x.value('@Name','varchar(50)') AS Email

FROM @xml.nodes('/EmployeeMaster/Employee') TempXML (x)

SELECT x.value('../@EmpNo', 'int') AS UserNo, x.value('../@Name',       'varchar(50)') AS Email, x.value('@Name', 'varchar(50)') AS ItemName

FROM @xml.nodes('/EmployeeMaster/Employee/EmpSub') TempXML (x)

Output

5. xml.nodes()

This method takes an XQuery statement and modify the xml data like as

DECLARE @EmpNo int

Set @EmpNo=12

SET @xml.modify ('replace value of                   (/EmployeeMaster/Employee/@EmpNo)[1]      with sql:variable("@EmpNo")')

SELECT @xml;

Output

DECLARE @EmpNo int

Set @EmpNo=1

SET @xml.modify(' delete            EmployeeMaster/Employee/EmpSub[@No=sql:variable("@EmpNo")]')

SELECT @xml;

Output

About Author:
Anand Sahayaraj is technology lover and is important part of Systems Plus technology Think Tank. He works in Systems Plus Pvt. Ltd. and actively contributes to technology. He can be contacted at: anand.s@spluspl.com