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

<xsd:schema targetnamespace="urn:schemas-microsoft-com:sql:sqlrowset1" xmlns:schema="urn:schemas-microsoft-com:sql:sqlrowset1" xmlns:xsd="http://www.w3.org/2001/xmlschema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementformdefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemalocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="employees">
    <xsd:complextype>
      <xsd:attribute name="employeeid" type="sqltypes:int" use="required" />
      <xsd:attribute name="firstname" use="required">
        <xsd:simpletype>
          <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeid="1033" sqltypes:sqlcompareoptions="ignorecase ignorekanatype ignorewidth" sqltypes:sqlsortid="52">
            <xsd:maxlength value="10" />
          </xsd:restriction>
        </xsd:simpletype>
      </xsd:attribute>
      <xsd:attribute name="lastname" use="required">
        <xsd:simpletype>
          <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeid="1033" sqltypes:sqlcompareoptions="ignorecase ignorekanatype ignorewidth" sqltypes:sqlsortid="52">
            <xsd:maxlength value="20" />
          </xsd:restriction>
        </xsd:simpletype>
      </xsd:attribute>
    </xsd:complextype>
  </xsd:element>
</xsd:schema>
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="1" firstname="nancy" lastname="davolio" />
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="2" firstname="andrew" lastname="fuller" />
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="3" firstname="janet" lastname="leverling" />
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="4" firstname="margaret" lastname="peacock" />
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="5" firstname="steven" lastname="buchanan" />
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="6" firstname="michael" lastname="suyama" />
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="7" firstname="robert" lastname="king" />
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="8" firstname="laura" lastname="callahan" />
<employees xmlns="urn:schemas-microsoft-com:sql:sqlrowset1" employeeid="9" firstname="anne" lastname="dodsworth" />

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文件

<employee id="1">
  <name>
    <firstname>nancy</firstname>
    <lastname>davolio</lastname>
  </name>
</employee>
<employee id="2">
  <name>
    <firstname>andrew</firstname>
    <lastname>fuller</lastname>
  </name>
</employee>
<employee id="3">
  <name>
    <firstname>janet</firstname>
    <lastname>leverling</lastname>
  </name>
</employee>
<employee id="4">
  <name>
    <firstname>margaret</firstname>
    <lastname>peacock</lastname>
  </name>
</employee>
<employee id="5">
  <name>
    <firstname>steven</firstname>
    <lastname>buchanan</lastname>
  </name>
</employee>
<employee id="6">
  <name>
    <firstname>michael</firstname>
    <lastname>suyama</lastname>
  </name>
</employee>
<employee id="7">
  <name>
    <firstname>robert</firstname>
    <lastname>king</lastname>
  </name>
</employee>
<employee id="8">
  <name>
    <firstname>laura</firstname>
    <lastname>callahan</lastname>
  </name>
</employee>

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文件

<myroot>
  <employees employeeid="1" firstname="nancy" lastname="davolio" />
  <employees employeeid="2" firstname="andrew" lastname="fuller" />
  <employees employeeid="3" firstname="janet" lastname="leverling" />
  <employees employeeid="4" firstname="margaret" lastname="peacock" />
  <employees employeeid="5" firstname="steven" lastname="buchanan" />
  <employees employeeid="6" firstname="michael" lastname="suyama" />
  <employees employeeid="7" firstname="robert" lastname="king" />
  <employees employeeid="8" firstname="laura" lastname="callahan" />
  <employees employeeid="9" firstname="anne" lastname="dodsworth" />
</myroot>