t-sql中的xml操作在我们平时做项目的过程中用的很少,因为我们处理的数据量很少,除非一些用到xml的地方,t-sql中xml操作一般用在数据量很大,性能优化的地方,当然我在平时做项目的时候也是没用过,但是学一点,以备不时之需。
今天就讲一下t-sql中简单的xml操作语法。
一,简单的xml操作
1,我们先建一个表:Student(id,content /xml)
示例代码:
create table Student (id int primary key,content xml) insert into dbo.Student values(1000,'<Students> <Student id="1001"> <name>aaa</name> <age>20</age> <birthday>1991-2-20</birthday> </Student> <Student id="1002"> <name>bbb</name> <age>21</age> <birthday>1990-2-20</birthday> </Student> </Students>')
2,添加学生节点,就是添加一个学生,用到modify的insert into语句,后面的/为xml节点的路径。
示例代码:
update dbo.Student set content.modify(' insert <Student id="1003"> <name>aaa</name> <age>20</age> <birthday>1991-2-20</birthday> </Student> as last into (/Students)[1] ')
3,添加属性,用到modify的insert into语句。
示例代码:
update dbo.Student set content.modify(' insert attribute sex {"男"} into (/Students/Student[@id="1003"])[1] ')
4,添加字段add,用到modify的insert into语句。
示例代码:
update dbo.Student set content.modify(' insert <add>江苏丰县</add> as last into (/Students/Student[@id="1003"])[1] ')
5,删除学生节点,用到modify的delete语句,[@id="1003"]为删除的条件,像t-sql中的where一样。
示例代码:
update dbo.Student set content.modify(' delete /Students/Student[@id="1003"] ')
6,更改学生节点字段,用到modify语句中的replace语句,text()表示的是add节点的值。
示例代码:
update dbo.Student set content.modify(' replace value of (/Students/Student[@id="1003"]/add/text())[1] with "江苏徐州" ')
7,更改学生节点属性,用到modify语句中的replace语句,@id表示的是add节点的属性的值。
示例代码:
update dbo.Student set content.modify(' replace value of (/Students/Student[@id="1003"]/@id)[1] with 1004 ')
8,查询所有学生的ID和姓名。
示例代码:
select Student1.content.value('./@id','int') as ID, Student1.content.value('(/Students/Student/name)[1]','nvarchar(30)') as StuName from dbo.Student CROSS APPLY content.nodes('/Students/Student') as Student1(content)
二,xml操作实例
上面说的都是xml一些简单的操作,下面我们结合t-sql中的xml操作,存储过程和事务做一个实例,以便我们更好的去理解,运用。
实例要求:定义一个存储过程,要求传递一个xml变量类型,将xml内的指定的ID记录,从Table1全部掉,删除操作要求利用事务;
1,首先我们需要建一张表,然后插一些数据。
示例代码:
create table Table1 ( ID int primary key, Name nvarchar(50) not null ) insert into dbo.Table1 values(1,'Name1'),(2,'Name2'),(3,'Name3') select * from Table1
2,实例要求我们需要建一个存储过程,然后传递一个xml变量,然后将xml内的指定的ID记录,从Table1全部掉,而且删除操作用事务。
我们存储过程就得将xml进行解析得到xml中的ID记录,这个操作我们就得用到游标,游标我会在以后的做讲解,游标遍历得到的ID记录,
查询Table1表中是否存在,如果存在记录下来,并用事务去删除。
示例代码:
create proc proc_Table1 ( @ID xml ) as begin declare @Temp table(ID1 int) insert into @Temp(ID1) select ParamValues123.ID2.value('./@id','int') as asdfasdf FROM @ID.nodes('/nodes/node') as ParamValues123(ID2) begin transaction t1 declare @j int; select @j=count(ID1) from @Temp; declare curs_Table1 cursor for select ID1 from @Temp; declare @ID2 int; declare @i int; set @i=0; open curs_Table1; fetch next from curs_Table1 into @ID2; while @@FETCH_STATUS = 0 begin if(exists(select ID from dbo.Table1 where ID=@ID2)) set @i=@i+1; fetch next from curs_Table1 into @ID2; end close curs_Table1; deallocate curs_Table1; if @i=@j begin delete from dbo.Table1 Where ID in ( SELECT ParamValues123.ID2.value('./@id','int') as ID FROM @ID.nodes('/nodes/node') as ParamValues123(ID2) ) commit transaction t1; end else rollback transaction t1; --drop table @Temp; --select * from Table1 Where ID in --( --SELECT ParamValues123.ID2.value('./@id','int') as asdfasdf --FROM @ID.nodes('/nodes/node') as ParamValues123(ID2) --) end
以上是t-sql中的xml简单用法,有错误的地方希望园友指正。
以后还会整理一些编程的知识分享给大家,希望大家多多关注。。。