XMLvariable AND as last

转载于 2010-10-30 23:30:00 发布 · 76 阅读

本文介绍如何使用SQL Server的XML数据类型及其modify方法来修改XML文档。包括替换节点值、插入属性及元素等操作。

--replace   with sql:variable("@SEX")

DECLARE @x1 XML

SELECT @x1 = '<People NAME="dongsheng" SEX="女"/>'

DECLARE @SEX1 NVARCHAR(10)

SELECT @SEX1 = '男'

SET @x1.modify('replace value of (/People/@SEX)[1]     with sql:variable("@SEX1")')



/*<People NAME="dongsheng" SEX="男" />*/

----insert attribute

DECLARE @x2 XML

SELECT @x2= '<People NAME="dongsheng" />'

DECLARE @SEX2 VARCHAR(15)

SELECT @SEX2 = '男'

SET @x2.modify('    insert attribute SEX {sql:variable("@SEX2")} as last into   (/People)[1]')


/*<People NAME="dongsheng" SEX="男" />*/

--insert element
DECLARE @x3 XML

SELECT @x3 ='<People NAME="dongsheng">  <SEX2>女</SEX2> </People>'

DECLARE @SEX3 VARCHAR(15)

SELECT @SEX3 = '男'

SET @x3.modify('    insert element SEX {sql:variable("@SEX3")} as last into

(/People)[1]')

SELECT @x3

/*

<People NAME="dongsheng">
  <SEX2>女</SEX2>
  <SEX>男</SEX>
</People>

*/