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 >