《SqlServer 系列》 - 操作XML类型字符串

最新推荐文章于 2022-05-31 08:47:38 发布

weixin_34358365

最新推荐文章于 2022-05-31 08:47:38 发布

阅读量384

点赞数

文章标签：数据库 python javascript ViewUI

原文链接：https://my.oschina.net/kimisme/blog/1605434

版权

2019独角兽企业重金招聘Python工程师标准>>>

一、定义变量

声明一个xml类型变量

DECLARE @xmlInfo XML;
SET @xmlInfo = 
'<CompanyGroup>
    <Company code ="0001">
       <Department>
          <Employee>
             <Name>Kimisme</Name>
             <Skill>C#</Skill>
          </Employee>
       </Department>
    </Company>
    <Company code ="0002">
       <Department>
          <Employee>
             <Name>Zhao</Name>
             <Skill>Java</Skill>
          </Employee>
       </Department>
    </Company>
</CompanyGroup>';

二、查询

(1)使用query(xquery)查询

SELECT @xmlInfo.query('/CompanyGroup/Company');

(2)使用value(xquery,dataType)查询

SELECT @xmlInfo.value('(/CompanyGroup/Company/Department/Employee/Name)[1]', 'nvarchar(max)');

(3)查询属性值

SELECT @xmlInfo.value('(/CompanyGroup/Company/Department/Employee/Name)[1]', 'nvarchar(max)');

(4)使用xpath进行查询

SELECT @xmlInfo.query('(/CompanyGroup/Company[@code="0001"])');

三、修改

(1)修改节点值

SET @xmlInfo.modify('replace value of(/CompanyGroup/Company[@code="0001"]/Department/Employee/Skill/text())[1] with "JavaScript"');

(2)删除节点

SET @xmlInfo.modify('delete /CompanyGroup/Company[@code="0002"]');

(3)添加节点

SET @xmlInfo.modify('insert <Employee><Name>Lily</Name><Skill>Photoshop</Skill></Employee> before (/CompanyGroup/Company[@code="0001"]/Department/Employee)[1]');

(4)添加属性

set @xmlInfo.modify('insert (attribute Name{"Develop"},attribute Date{"2016-1-1"}) into (/CompanyGroup/Company[@code="0001"]/Department)[1]')

(5)删除属性

SET @xmlInfo.modify('delete /CompanyGroup/Company[@code="0002"]/@code');

(6)修改属性

SET @xmlInfo.modify('replace value of (/CompanyGroup/Company[@code="0002"]/@code)[1] with "0003"');

四、判存

SELECT *
FROM Device
WHERE Propertys.exist('/DeviceExtInfo/AttributeList/AttributeInfo[realName = "IsChangeValve" ]') = 1
AND MeterNumber IS NOT NULL;

转载于:https://my.oschina.net/kimisme/blog/1605434