create xml schema collection DhzTest as'<?xml version="1.0" encoding="utf-8"?><xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="ROOT"> <xs:complexType> <xs:sequence> <xs:element name="item"> <xs:complexType> <xs:sequence> <xs:element name="fileName" type="xs:string" /> <xs:element name="fileLength" type="xs:decimal" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element></xs:schema>'--Create Table XmlTest(IDX int identity(1,1),com xml(DhzTest)) select * From XMLTEST--1--insert into xmltest(com)values(Convert(xml,N'<ROOT><item><fileName>dhz.jpg</fileName><fileLength>200.0</fileLength></item></ROOT>'))--2--declare @xml xml(dhzTest)set @xml='<ROOT> <item> <fileName>pc.htm</fileName> <fileLength>12.0</fileLength> </item></ROOT>'insert into xmlTest(com) values(@xml)--select * FRom XMLTest
select FN=AssetXML.query('/manifest/items/item[2]/fileName/text()') From Asset Where AssetID=7--declare @xml xmlselect @xml=assetXml From Asset Where AssetID=7set @xml.modify(' replace value of (/manifest/items/item[2]/fileName/text())[1] with "dhz.swf"')select @xml----update Asset set AssetXML.modify(' replace value of (/manifest/items/item[2]/fileName/text())[1] with "test.swf"')Where AssetID=7
