-- 作者:admin
-- 发布时间:2009/12/24 8:52:18
-- SQL Server 2005数据库的XML简单使用
1、XML类型 declare @mydata XML set @mydata=\'<students><student><number>000001</number><name>黎明</name></student><student><number>000002</number><name>赵怡春</name></student></students>\' select @mydata
2、FOR XML子句(从关系表到XML) 2、1 raw 属性中心: select * from stu for XML raw
元素中心; select * from stu for XML raw,elements
2、2 auto 类似于raw,但是每一行的名称不再是row而是表名称 select * from stu for XML raw,elements
几个练习: 多表显示 select * from stu inner join grade on stu.number=grade.number for xml auto select stu.number,name,course,grade from stu inner join grade on stu.number=grade.number for xml auto
嵌套次序发生改变(只是利用投影字段的名称次序) select course,grade,stu.number,name from stu inner join grade on stu.number=grade.number for xml auto
显示schema信息 select * from stu for xml auto,XMLDATA,ELEMENTS
2、3 explicit select 1 as TAG, NULL as PARENT,stu.number as [stus!1!sid],stu.name as [stus!1!name] from stu for xml explicit 显示如: <stus sid="000001" name="黎明"/> <stus sid="000002" name="赵怡春"/> <stus sid="000003" name="张富平"/> <stus sid="000004" name="白丽"/> ...
说明: 1)1 as TAG, NULL as PARENT是不能省略的,表示当前定义的标签号和父节点,NULL表示没有 2)[stus!1!sid]:[元素名称!元素号!属性名称]
事实上,上述结果也可以使用其他方式得到: select number as sid, name from stu for xml auto
更为复杂的形式: SELECT 1 as Tag, NULL as Parent, stu.number as [stu!1!number], NULL as [grade!2!course], NULL as [grade!2!grade] FROM stu UNION ALL SELECT 2, 1, stu.number, grade.course, grade.grade FROM stu inner join grade on stu.number=grade.number ORDER BY [stu!1!number],[grade!2!course] --for xml explicit
说明:对于两个投影字段不一致的查询可以通过调整字段名称来实现,注意第二个查询可以省略字段名称,默认和第一个查询一样,如: select number,name as \'a\',null as \'course\',null from stu union all select number,null ,course,grade from grade order by number,course
3、使用OPENXML(从XML到关系表) DECLARE @hdoc int DECLARE @doc nvarchar(1000) --使用汉字需用nvarchar
SET @doc =\' <root> <stu sid= "000001" name="黎明" sex="1"> <grade cid="A03" grade="56"/> <grade cid="A02" grade="90"/> </stu> <stu sid= "000002" name="赵怡春" sex="0"> <grade cid="A03" grade="60"/> <grade cid="B01" grade="61"/> </stu> </root> \'
--Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT * FROM OPENXML (@hdoc, \'/root\') EXEC sp_xml_removedocument @hdoc
还可以尝试 DECLARE @hdoc int DECLARE @doc nvarchar(1000)
SET @doc =\' <root> <stu sid= "000001" name="黎明" sex="1"> <grade cid="A03" grade="56"/> <grade cid="A02" grade="90"/> </stu> <stu sid= "000002" name="赵怡春" sex="0"> <grade cid="A03" grade="60"/> <grade cid="B01" grade="61"/> </stu> </root> \'
--Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT * FROM OPENXML (@hdoc, \'/root/stu/grade\',3) WITH (cid varchar(6),name varchar(10) \'../@name\',UniqueIDVal int \'@mp:id\',NodeName varchar(10) \'@mp:localname\',NodeSibling varchar(10) \'@mp:prev\') EXEC sp_xml_removedocument @hdoc
[此贴子已经被作者于2010-12-11 20:26:30编辑过]
|