articles >> developer >> Working with XML Data in SQL Server ...

Working with XML Data in SQL Server 2005

By : S.Srivathsani Aug 06, 2007

Page 2 / 3



3) Exist method
The exist method takes an expression as input.This expression selects a single node within the XML document and returns true(1) if that node exists or false(0) if it does not.

Example:
select Experimentchemical.exist('/root/ChemicalName') from students
The above query will return true for all items where the student has the chemical details to be used in the experiment.



The exist method can be used in the where clause in the following way:
select * from students where Experimentchemical.exist('/root/ChemicalName')=1

4)Modify Method
Modify method can be used to manipulate the XML data stored in a table.The modify method consists of three substatements:

• INSERT
• DELETE
• REPLACE

Example:
1)
declare @x xml
declare @custid int
set @x='<root>
<CustomerDescription CustID="101" CustomerName="ABCL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
<CustomerDescription CustID="102" CustomerName="HAL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
</root>'
select @x
set @x.modify('delete /root/CustomerDescription/@CustID')
select @x
Result:
<root>
<CustomerDescription CustomerName="ABCL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
<CustomerDescription CustomerName="HAL Industries Limited">
<Phonenumber>
<Work>1234567890</Work>
<Residence>1434546678</Residence>
</Phonenumber>
</CustomerDescription>
</root>

2)
To insert a new node or fragment of XML,we can use the INSERT statement.The syntax for it is as follows:
Syntax:
insert
Expression1 (
{as first | as last} into | after | before
Expression2
)

Arguments:
Expression1
Identifies one or more nodes to insert.
into
Nodes identified by Expression1 are inserted as direct descendents (child nodes) of the node identified by Expression2. If the node in Expression2 already has one or more child nodes, the user must use either as first or as last to specify where the new node has to be added. For example, at the start or at the end of the child list, respectively.
after
Nodes identified by Expression1 are inserted as siblings directly after the node identified by Expression2.
before
Nodes identified by Expression1 are inserted as siblings directly before the node identified by Expression2.
Expression2
Identifies a node. The nodes identified in Expression1 are inserted relative to the node identified by Expression2.

Example:
USE AdventureWorks;
GO
DECLARE @myDoc xml      
SET @myDoc = '<Root>      
<ProductDescription ProductID="1" ProductName="Road Bike">      
<Features>      
</Features>      
</ProductDescription>       
</Root>'      
SELECT @myDoc      
-- insert first feature child (no need to specify as first or as last)      
SET @myDoc.modify('      
insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
into (/Root/ProductDescription/Features)[1]')
SELECT @myDoc 

Result:
<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>
For more examples,refer to” insert(XML DML)” in SQL Server 2005 books online.

5) Nodes Method
The nodes method can be used to extract data from an XML document and use that to generate subnodes that can be used for various purposes,such as,to create new content or insert content into new tables.
Example:
declare @x xml
set @x='<Root><row id="1"><AirportCode>MUM</AirportCode><AirportName>Mumbai</AirportName></row>
<row id="2"><AirportCode>MAS</AirportCode><AirportName>Madras</AirportName></row>
<row id="3"></row></Root>'

Select T.c.query('.') as result
from @x.nodes('/Root/row') T(c)