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.
SELECT @xml.value('/EmployeeMaster[1]/Employee[2]/@Name', 'VARCHAR(4)') as ResultName
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
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
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