SQLServer2005讲课代码.docVIP

SQLServer2005讲课代码 一、select..........for xml 模式，命令 --FOR XML语句的使用,注意观察运算结果 select * from student where stu_num in(M070101,M070102) --for xml raw select * from student where stu_num in(M070101,M070102) for xml raw,elements select * from student where stu_num in(M070101,M070102) for xml raw(Student),root(Students) --for xml auto select * from student where stu_num in(M070101,M070102) for xml auto select * from student where stu_num in(M070101,M070102) for xml auto,elements --for xml path select stu_num @snum,stu_name from student where stu_num in(M070101,M070102) for xml path ----Path模式的使用 --select stu_num @Num, -- stu_name Infor/name, -- stu_address Infor/Address --from student --where stu_num in(M070201,M070302) --for xml path -- --select stu_num @Num, -- stu_name Infor/name, -- stu_address Infor/Address --from student --where stu_num in(M070201,M070302) --for xml path(Student) --for xml explicit select 1 as Tag,null as Parent, stu_num as [student!1!stu_num], stu_name as [student!1!stu_name] from student where stu_num in(M070101,M070106) for xml explicit select 1 as Tag,null as Parent, stu_num as [student!1!stu_num], stu_name as [student!1!stu_name!element] from student where stu_num in(M070101,M070106) for xml explicit 二、openxml(xml文档句柄，模式（访问路径），标识量) with （表的定义） 1.定义文档句柄和文档变量 2.使用sp_xml_preparedocment对xml文档进行解析，返回句柄 3.读取xml文档的某个节点或者（一个或者多个属性值） 4.使用sp_xml_removedocment销毁句柄 --1.接收XML文档 declare @xmlhandle int declare @xmldoc varchar(1000) set @xmldoc= students student stu_num=001 stu_name=zhangsan /student student stu_num=002 stu_name=lisi /student student stu_num=003 stu_name=wangwu /student /students --生成内部树的表示sp_xml_preparedocument exec sp_xml_preparedocument @xmlhandle output,@xmldoc --使用openxml函数检索行集 --openxml(句柄，行模式，标志位) with(表格定义) select * from openxml(@xmlhandle,/students/student) with(Num varchar(10) @stu_num,Name varchar(20) @stu_name) --当文档既有属性又有子节点时如何读取(碎分) declare @xm