sql for xml

for xml子句有四种最基本的模式，如上图所示：

1、auto模式：返回数据表为起表名的元素，每一列的值返回为属性；
2、raw模式：返回数据行为<row>元素，每一列的值作为<row>元素的属性；
3、path模式：通过简单的xpath语法来答应用户自定义嵌套的xml结构、元素、属性值
4、explicit模式：通过select语法定义输出xml的结构

 

具体实例如下：

1、auto模式

(1). sql语句：

   1:  select employeeid,firstname,lastname from employees for xml auto, xmlschema

(2). 所生成的xml文件：

返回xml文件的xml schema

2、raw模式

(1). sql语句：

   1:  ----将元素命名为自定义的名称employee

   2:  select employeeid,firstname,lastname from employees for xml raw ('employee')

(2). 所生成的xml文件：

   1:  <employee employeeid="1" firstname="nancy" lastname="davolio" />

   2:  <employee employeeid="2" firstname="andrew" lastname="fuller" />

   3:  <employee employeeid="3" firstname="janet" lastname="leverling" />

   4:  <employee employeeid="4" firstname="margaret" lastname="peacock" />

   5:  <employee employeeid="5" firstname="steven" lastname="buchanan" />

   6:  <employee employeeid="6" firstname="michael" lastname="suyama" />

   7:  <employee employeeid="7" firstname="robert" lastname="king" />

   8:  <employee employeeid="8" firstname="laura" lastname="callahan" />

   9:  <employee employeeid="9" firstname="anne" lastname="dodsworth" />

3、path模式：

(1). sql语句：

select employeeid "@id",firstname  "name/firstname",lastname "name/lastname"

from employees for xml path ('employee')

(2). 所生成的xml文件

4、explicit模式

问题：加入要生成如下的xml文档该如何操作？

<employee empid="1">

<firstname>nancy</firstname>

<lastname>davolio</lastname>

</employee>

explicit模式解决这个问题的应用分为两个主要步骤：

1.定义要输出的xml文档结构；

2.传入实际的数据值；

(1). sql语句：

--定义输出xml文档的数据结构

select  1 as tag,

        null as parent,

        employeeid as [employee!1!empid],

        firstname as [employee!1!firstname!element],

        lastname as [employee!1!lastname!element]

from    employees

union all

--传入实际的数据

select  1,

        null,

        employeeid,

        firstname,

        lastname

from    employees

order by [employee!1!empid],

        [employee!1!firstname!element],

        [employee!1!lastname!element]

for     xml explicit

语句含义的解释：

先看看定义xml结构的语句输出结果：

tag栏用来指定生成元素的嵌套水平；1表示嵌套水平为

parent栏用来指定当前tag的父级层次；null值表示该元素为顶级元素；

employeeid as [employee!1!empid],

说明：当前元素或属性的父级元素的名称！元素的标签号！元素或属性的名称
firstname as [employee!1!firstname!element],

说明：当前元素或属性的父级元素的名称！元素的标签号！元素或属性的名称！指定值作为元素输出

5、为输出的xml文档添加根元素(root element)

(1). sql语句：

select employeeid,firstname,lastname from employees for xml auto, root('myroot')

(2). 所生成的xml文件