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 xml
select @xml=assetXml From Asset Where AssetID=7
set @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