SQL2005中XML列的操作


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

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页