SQL2005中XML列的操作

最新推荐文章于 2024-08-30 15:48:24 发布

dhz123

最新推荐文章于 2024-08-30 15:48:24 发布

阅读量1.1k

点赞数

文章标签： xml sql schema insert encoding string

本文链接：https://blog.csdn.net/dhz123/article/details/563383

版权

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