你的位置:首页 > 数据库

[数据库]SqlServer知识点


一、开发环境

SQL2010

二、开发过程

1.声明一个
DECLARE @ SET @ = 
'<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 @.query('/CompanyGroup/Company');

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

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

(3)查询属性值

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

(4)使用xpath进行查询

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

3.修改操作

(1)修改节点值

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

(2)删除节点

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

(3)添加节点

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

(4)添加属性

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

(5)删除属性

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

(6)修改属性

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

三、参考文章

http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html