一、开发环境
SQL2010
二、开发过程
1.声明一个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>';
2.查询操作
(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"])');
3.修改操作
(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"');
4.其他方法
(1)判存
SELECT * FROM Device WHERE Propertys.exist('/DeviceExtInfo/AttributeList/AttributeInfo[realName = "IsChangeValve" ]') = 1 AND MeterNumber IS NOT NULL;
三、参考文章
http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html