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