Sqlserver xml 类型操作

最基本的规则：
xml类型的数据之间以及xml类型与其它数据类型之间都是不能比较的，也就是说xml类型的数据不能出现在等号的任何一边。

对 咱们数据库来说，本来是不想让大家使用XML的数据类型的。既然这东西很好用，而且平台和各个产品线都在使用这个数据类型，对XML做以下要求：
1、 不允许批量更新XML数据。更新的时候，在Where 条件句里面必须要加上限制条件，如：userid.
2、不允许批量查询XML类型的。查询 也需要在Where条件句上加上条件，如userid。在加上xml数据的条件。

查询类
1. query()方法：返回满足条件的所有xml行。只能用于select子句当中。
2. value()方法：返回从xml节点中提取的标量值。必须在value()方法的第二个参数中指定所返回的标量的数据类型，所以value()方法可以 与其它标量进行比较。可用于select子句和where子句。
3. exist()方法：返回int型标量的0或者1。对每行的xml数据类型进行存在性检查。可用于select子句和where子句。
4. nodes()方法：返回只有一个栏位的table，且该table的栏位是xml数据类型。所以nodes()方法只能出现在from子句中。

修 改类：
modify()
modify(insert .....)
modify(delete .....)
modify(replace ....)

用公司的 beisenuser 数据库里面的AssessmentUserTestResult 表做试验。

--查询函数
select ID,fk_beisenuser_id,Result
--select *
from AssessmentUserTestResult
where ID = 1
--查询
--query()方法
--查询 part节点所有xml数据
select id,fk_beisenuser_id,result.query('/TestResult/test/partlist/part')
from AssessmentUserTestResult
where ID = 2

select id,fk_beisenuser_id,result.query('/TestResult/test/partlist/part/starttime')
from AssessmentUserTestResult
where ID = 2

select id,fk_beisenuser_id,result.query('/TestResult/test/partlist/part/questionlist/question')
from AssessmentUserTestResult
where ID = 2

select id,fk_beisenuser_id,result.query('/TestResult/test/partlist/part/questionlist/question[1]')
from AssessmentUserTestResult
where ID = 2

select id,fk_beisenuser_id,result.query('/TestResult/test/partlist/part/questionlist/question[2]')
from AssessmentUserTestResult
where ID = 2

--value()方法

select id,fk_beisenuser_id,result.value('(/TestResult/test/partlist/part/starttime)[1]','datetime') as starttime
from AssessmentUserTestResult
where ID = 2

select Result.value('(/TestResult/test/partlist/part/questionlist/question[1]/id)[1]','varchar(500)')
from AssessmentUserTestResult
where ID = 2

select *
from AssessmentUserTestResult
where Result.value('(/TestResult/test/partlist/part/questionlist/question/id)[1]','uniqueidentifier') = '2142FF59-BBCA-4D30-8325-F188564EE109'
and ID = 2

select *
from AssessmentUserTestResult
where Result.value('(/TestResult/test/partlist/part/questionlist/question[2]/id)[1]','uniqueidentifier') = '68A0D7B3-CF21-4BB4-91C0-F93D0AB35F09'
and ID = 2

--exist() 方法

select *
from AssessmentUserTestResult
where Result.exist('(/TestResult/test/partlist/part/questionlist/question[1]/result)')= 1
and id = 2

--node()方法

--何老板可能会对下面这个结果感兴趣。


select b.loc.query('.')
from AssessmentUserTestResult a
cross apply result.nodes('/TestResult/test/partlist/part/questionlist/question') as b(loc)
where a.ID = 2

--修改数据
--在part节点内部插入xml数据

--modify(insert) 参数 as first,as last,before,after
update AssessmentUserTestResult
set Result.modify('
insert
<question>
<id>aaaaaa-27E7-4240-8455-bbbbbbbbbbbbb</id>
<result>1</result>
</question>
into (/TestResult/test/partlist/part)[1]'
)
where ID = 1
-------------------
update AssessmentUserTestResult
set Result.modify('
insert
<question>
<id>aaaaaa-27E7-4240-8455-bbbbbbbbbbbbb</id>
<result>1</result>
</question>
as last into (/TestResult/test/partlist/part)[1]'
)
where ID = 1




--modify(delete)
--
-- 删除内容 
update AssessmentUserTestResult
set Result.modify('
delete  (/TestResult/test/partlist/part/questionlist/question)[1]/id/text()'
)
where ID = 1

--删除节点

update AssessmentUserTestResult
set Result.modify('
delete  (/TestResult/test/partlist/part/questionlist/question[1]/id)[1]'
)
where ID = 1

--删除属性

update AssessmentUserTestResult
set Result.modify('
delete  (/TestResult/test/partlist/part/questionlist/question[1]/@category)[1]'
)
where ID = 1

select result.query('(/TestResult/test/partlist/part/questionlist/question)[1]')
from AssessmentUserTestResult
where ID = 1

--modify(replace)
-- 替换内容

select result.query('(/TestResult/test/partlist/part/questionlist/question[2])')
from AssessmentUserTestResult
where ID = 1

update AssessmentUserTestResult
set Result.modify('
replace value of  (/TestResult/test/partlist/part/questionlist/question[2]/result[1]/text())[1]
with ("4")'
)
where ID = 1

/*
--替换属性
update AssessmentUserTestResult
set Result.modify('
replace value of  (/TestResult/test/partlist/part/questionlist/question[2]/@result)[1]
with ("aaaaa")'
)
where ID = 1

*/

----------------------------------------------------------------------------无耻的分割-------------------------------------------------------------

declare @personid int ,@wdid uniqueidentifier,@value int

declare my_cursor cursor for
select perid,wdid,value from temp_duoyu
open my_cursor
fetch next from my_cursor into @personid,@wdid,@value
while @@FETCH_STATUS = 0
begin

update a
set testcent.modify('
replace value of (/TestCent/test[4]/level1originalcent/centlist[id=sql:variable("@wdid")]/value/text())[1]
with sql:variable("@value")
')
from AssessmentUserTestResult a
where a.ID = @personid

fetch next from my_cursor into @personid,@wdid,@value
end
close my_cursor
deallocate my_cursor

----------------------------------------------------------------------------无耻的分割-------------------------------------------------------------