sql for xml 四种模式 例子

---创建表数据
USE boris
IF OBJECT_ID('Student') IS NOT NULL
DROP TABLE Student
CREATE TABLE  Student
(Sname nvarchar(50) null, Ssex nvarchar(50) null,Sage int null)
insert into Student
select '张三','男','14' union all
select '李四','女','15'union all
select '王五','男','16'

IF OBJECT_ID('Mid_score') IS NOT NULL
DROP TABLE Mid_score
CREATE TABLE Mid_score(Name nvarchar(50) null,Course nvarchar(50) null,Score decimal(18,1) null)
INSERT INTO Mid_score
select '张三','物理','70' union all
SELECT '张三','化学','80' UNION ALL
SELECT '张三','地理','90' UNION ALL
SELECT '李四','物理','88' UNION ALL
SELECT '李四','化学','77' UNION ALL
SELECT '李四','地理','78' UNION ALL
SELECT '王五','物理','67' UNION ALL
SELECT '王五','化学','89' UNION ALL
SELECT '王五','地理','87'
IF OBJECT_ID('High_score') IS NOT NULL
DROP TABLE High_score
CREATE TABLE High_score(Name nvarchar(50) null,Course nvarchar(50) null,Score decimal(18,1) null)
INSERT INTO High_score
select '张三','语文','70' union all
SELECT '张三','数学','80' UNION ALL
SELECT '张三','英语','90' UNION ALL
SELECT '李四','语文','90' UNION ALL
SELECT '李四','数学','99' UNION ALL
SELECT '李四','英语','88' UNION ALL
SELECT '王五','语文','78' UNION ALL
SELECT '王五','数学','67' UNION ALL
SELECT '王五','英语','89'


-----XML查询的四种模式
  --2012.11.6
select Sname, Sage, Ssex from dbo.Student
/*
Sname                                              Sage                                               Ssex
---------------------------  -------------------------------------------------- ----------------------------------------------
张三                                                 14                                                 男
李四                                                 15                                                 女
王五                                                 16                                                 男

*/

--for xml raw返回数据行为元素，每一列的值作为元素的属性
select Sname, Sage, Ssex from dbo.Student for xml raw
/*
<row Sname="张三 " Sage="14" Ssex="男"/>
<row Sname="李四 " Sage="15" Ssex="女"/>
<row Sname="王五" Sage="16" Ssex="男"/>
*/
select Sname, Sage, Ssex from dbo.Student for xml raw ('test')  
/*
<test Sname="张三 " Sage="14" Ssex="男"/>
<test Sname="李四 " Sage="15" Ssex="女"/>
<test Sname="王五" Sage="16" Ssex="男"/>
*/
select Sname, Sage, Ssex from dbo.Student for xml raw,elements
/*
  <row>
    <Sname>张三 </Sname>
    <Sage>14 </Sage>
    <Ssex>男</Ssex>
  </row>
  <row>
    <Sname>李四 </Sname>
    <Sage>15</Sage>
    <Ssex>女</Ssex>
  </row>
  <row>
    <Sname>王五</Sname>
    <Sage>16</Sage>
    <Ssex>男</Ssex>
  </row>
*/


---AUTO模式：返回数据表为起表名的元素，每一列的值返回为属性
select Sname, Sage, Ssex from dbo.Student for xml auto
/*
<dbo.Student Sname="张三 " Sage="14" Ssex="男"/>
<dbo.Student Sname="李四 " Sage="15" Ssex="女"/>
<dbo.Student Sname="王五" Sage="16" Ssex="男"/>
*/
select s.*,m.Course,m.score from dbo.Student s ,Mid_score m where s.sname=m.name  for xml auto
---多表时可做嵌套格式, select 顺序不一样是得到的结果格式也不一样
/*
<s Sname="张三" Sage="14" Ssex="男">
  <m Course="物理" score="70" />
  <m Course="化学" score="80" />
  <m Course="地理" score="90" />
</s>
<s Sname="李四" Sage="15" Ssex="女">
  <m Course="化学" score="77" />
  <m Course="地理" score="78" />
  <m Course="物理" score="88" />
</s>
<s Sname="王五" Sage="16" Ssex="男">
  <m Course="地理" score="87" />
  <m Course="物理" score="67" />
  <m Course="化学" score="89" />
</s>
*/
--PATH模式：通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值
select Sname, Sage, Ssex from dbo.Student for xml path,root('学生信息')--root('')添加根元素
/***
<学生信息>
  <row>
    <Sname>张三 </Sname>
    <Sage>14 </Sage>
    <Ssex>男</Ssex>
  </row>
  <row>
    <Sname>李四 </Sname>
    <Sage>15</Sage>
    <Ssex>女</Ssex>
  </row>
  <row>
    <Sname>王五</Sname>
    <Sage>16</Sage>
    <Ssex>男</Ssex>
  </row>
</学生信息>
***/



---EXPLICIT模式：通过SELECT语法定义输出XML的结构

---两张表 Student、Mid_score 输出成绩XML格式
 select distinct 1 as tag, null as parent,Sname[Student!1!Sname], Sage[Student!1!Sage], Ssex[Student!1!Ssex],null as[Mid_score!2!Course!xml],null as[Mid_score!2!Score!xml] from  Student  
 union all
 select  2 as tag, 1 as parent,Sname,Sage,Ssex,m.Course,m.Score  from dbo.Student s,Mid_score m where s.Sname =m.Name
 ORDER BY [Student!1!Sname],[Mid_score!2!Course!xml] FOR XML EXPLICIT

---三张表 Student、Mid_score、High_score 输出成绩XML格式
 select distinct 1 as tag, null as parent,Sname[Student!1!Sname], Sage[Student!1!Sage], Ssex[Student!1!Ssex],null as[Mid_score!2!Course!xml],null as[Mid_score!2!Score!xml], null as[High_score!3!Course!xml],null as [High_score!3!Score!xml]from dbo.Student union all select 2 as tag, 1 as arent,Sname,Sage,Ssex,m.Course,m.Score,null,null from dbo.Student s,Mid_score m where s.Sname =m.Name union all select  3 as tag, 1 as parent,Sname,Sage,Ssex,null,null,h.Course,h.Score from dbo.Student s,dbo.High_score h where s.Sname =h.Name order by [Student!1!Sname],tag FOR XML explicit