Monday, 18 November 2013

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

No comments:

Post a Comment