Query XML Data in Sql 2005

1. Sql Server 2005 includes subset of the XQuery language

2. Xquery is a language for query data stored using Xml Data Type

3. DDL operation on xml column

Syntax to retrieve a date from a column of xml data type<
Select columnname .query (‘/nodename’) from TableName
Select columname .query (‘/nodename/childnodename’) from TableName
Note: other clause of select such as ’ where’ clause can be use in above
statements and column should be xml type nodename refer to element name
4. DML operation on Xml



* INSERT


Syntax : Insert into tablename (columname,Xmlcolumname) values (1
,”Xquery”)
Note :xmlcolumn name can be second or third column according to table definition and valid xml data should be inserted in xml column

* UPDATE


I)as first II) as last III)into Iv)after V)before VI)Delete VII)Replace value of

As Last & As First

Syntax:Update table name set columnname.modify(‘insert microsoft
as last into (/book[1]) ’)
Note: as last and as first same syntax .column should be xml type

After & before

Syntax:Update table name set columname.modify(‘insert james
after into (book /pubs[1]) ’)
Note: after and before same syntax .column should be xml type

Delete

Syntax:Update table name set columname.modify(‘ delete /book/pubs/auother ’)
Note:Enter element name with path colum should be xml type

Replace value of particular element

Syntax:Update table name set columname.modify(‘ replace value of
(book /pubs/text())[1] with ‘‘sql’’ ’)
Note:Enter element name with path colum & text() colum should be xml type


Refered links
http://msdn.microsoft.com/en-us/library/ms345122(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms971534.aspx
http://msdn.microsoft.com/en-us/library/ms345117.aspx
http://blogs.msdn.com/mrorke/archive/2005/04/13/407921.aspx