Query XML Data in Sql 2005

que

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