SQL Server中操作xml数据

1、xml的格式

2、创建测试数据

--创建测试表
CREATE TABLE testxml (FID NVARCHAR(1),xmlbody xml)

--插入测试数据
INSERT INTO testxml(FID,xmlbody) VALUES(1,
'<ROOT>
	<NODE category="node1的属性">
		<CONTENT>c1</CONTENT>
	</NODE>
	<NODE category="node2的属性">
		<CONTENT>内容1</CONTENT>
		<CONTENT2>内容2</CONTENT2>
	</NODE>
</ROOT>
'
);

INSERT INTO testxml values(2,NULL);

3、判断某节点或者节点的属性是否存在——exist

--判断节点是否存在
SELECT xmlbody.exist('/ROOT/NODE/CONTENT'),fid FROM testxml where fid=1;  --返回1
SELECT xmlbody.exist('/ROOT/NODES/CONTENT'),fid FROM testxml where fid=1;  --返回0
SELECT xmlbody.exist('/ROOT'),fid FROM testxml where fid=1; --返回NULL

--判断属性是否存在与上面同理,属性名前面加@
SELECT xmlbody.exist('/ROOT/NODE/@category'),fid FROM testxml WHERE fid=1;  --返回1

4、获取节点或者属性的值——value

--获取对应节点下的所有内容，如果没有找到相关数据，返回NULL
SELECT xmlbody.value('/ROOT[1]/NODE[2]','varchar(max)'),fid FROM testxml;

--获取节点的属性值，如果没有找到相关数据，返回NULL
SELECT xmlbody.value('/ROOT[1]/NODE[2]/@category','varchar(max)'),fid FROM testxml;

5、获取节点的值返回xml类型——query

--返回的为xml类型的数据，如果不指明到最末级的节点，同一根路径下的同名节点会都获取到
SELECT xmlbody.query('/ROOT[1]/NODE[1]'),fid FROM testxml WHERE fid=1;
SELECT xmlbody.query('/ROOT[1]/NODE'),fid FROM testxml WHERE fid=1;
SELECT xmlbody.query('/ROOT/NODE'),fid FROM testxml WHERE fid=1;

SELECT xmlbody.query('/ROOT[1]/NODE[1]/CONTENT'),fid FROM testxml WHERE fid=1;

6、对xml节点进行增、删、改

• 增

--插入节点之使用四个参数at first、at last、before、after插入

--as first参数 插入指定路径下的第一个节点
update testxml SET xmlbody.modify('insert <first name="at first"/> as first into (/ROOT[1]/NODE[1])') WHERE fid=1;

--as last参数 插入指定路径下的最后一个节点
update testxml SET xmlbody.modify('insert <last name="at last"/> as last into (/ROOT[1]/NODE[1])') WHERE fid=1;

--before参数 插入到指定节点之前
UPDATE testxml SET xmlbody.modify('insert <before name="before"/> before (/ROOT[1]/NODE[1])') WHERE fid=1;

--after参数 插入到指定节点之后
update testxml SET xmlbody.modify('insert <after name="after"/> after (/ROOT[1]/NODE[1])') WHERE fid=1;

--插入节点之直接插入，不使用参数，直接插入到指定节点的最后面，如果插入多个节点，用“，”分隔开
UPDATE testxml SET xmlbody.modify('insert (<end1 name="end1"/>,<end2>end2</end2>) into (/ROOT[1]/NODE[1])') WHERE fid=1; 

--插入节点的属性值，如果插入多个属性，用“，”分隔开
UPDATE testxml SET xmlbody.modify('insert (attribute 属性1 {"end2的属性"},attribute 属性2 {"end2的属性2"}) into (/ROOT[1]/NODE[1]/end2[1])') WHERE fid=1;

--插入节点的内容，同样具有as first、as last、before、after四种选项
UPDATE testxml SET xmlbody.modify('insert text {"end1的内容"} into (/ROOT[1]/NODE[1]/end1[1])') WHERE fid=1;

• 删

--删除内容
UPDATE testxml SET xmlbody.modify('delete /ROOT[1]/NODE[1]/end1[1]/text()') WHERE fid=1;

--删除属性
UPDATE testxml SET xmlbody.modify('delete /ROOT[1]/NODE[1]/end2[1]/@属性2') WHERE fid=1;

--删除节点
UPDATE testxml SET xmlbody.modify('delete /ROOT[1]/NODE[2]/CONTENT2[1]') WHERE fid=1;

• 改

--替换属性
UPDATE testxml SET xmlbody.modify('replace value of (/ROOT[1]/NODE[1]/end2[1]/@属性1) with ("替换后的属性")') WHERE fid=1;

--替换内容
UPDATE testxml SET xmlbody.modify('replace value of (/ROOT[1]/NODE[2]/CONTENT[1]/text()[1]) with ("替换后的内容")') WHERE fid=1;