数据库基础与应用课件

发布XML文档

1FOR

XML

EXPLICITSQL

Server发布XML文档-FORXMLEXPLICIT解决方法：赋予列的别名特殊的含义：用于指定如何产生元素RAW和AUTO为查询结果的每一行产生一个元素，每列或作为这个元素的属性，或作为子元素。如果每一行的列之间需要有嵌套关系，就需要EXPLICIT和PATHEXPLICIT要求SELECT子句：前2列是整数值，分别叫做TAG和PARENT其他的列必须指定别名，别名起产生规则的作用，格式为：

ElementName!TagNumber!AttributeName!DirectiveSQL

Server发布XML文档-FORXMLEXPLICIT例11.15

查询所有学生的信息，每列作为一个元素

SELECT

1

AS

TAG,

0

AS

PARENT,

Sno

AS

[Student!1!Sno!ELEMENT],

Sname

AS

[Student!1!Sname!ELEMENT],

Ssex

AS

[Student!1!Ssex!ELEMENT],

Sage

AS

[Student!1!Sage!ELEMENT],

Sdept

AS

[Student!1!Sdept!ELEMENT]FROM

StudentFOR

XML

EXPLICITSQL

Server发布XML文档-FORXMLEXPLICITTAGPARENT[Student!1!Sno!ELEMENT][Student!1!Sname!ELEMENT].........102000012王林男19计算机102000113张大民男18管理102000256顾芳女19管理102000278姜凡男19管理102000014葛波女18计算机<Student>

<Sno>2000012</Sno>

<Sname>王林</Sname>

<Ssex>男</Ssex>

<Sage>19</Sage>

<Sdept>计算机</Sdept></Student><Student>

<Sno>2000113</Sno>

<Sname>张大民</Sname>

<Ssex>男</Ssex>

<Sage>18</Sage>

<Sdept>管理</Sdept></Student>SQL

Server发布XML文档-FORXMLEXPLICITTAGPARENT[Student!1!Sno!ELEMENT][Student!2!Sname!ELEMENT].........102000012王林男19计算机102000113张大民男18管理102000256顾芳女19管理102000278姜凡男19管理102000014葛波女18计算机<Student>

<Sno>2000012</Sno></Student><Student>

<Sno>2000113</Sno></Student>SQL

Server发布XML文档-FORXMLEXPLICITSELECT

1

AS

TAG,

0

AS

PARENT,

Sno

AS

[Student!1!Sno!ELEMENT],

null

AS

[Student!2!Sname!ELEMENT],

null

AS

[Student!2!Ssex!ELEMENT],

null

AS

[Student!2!Sage!ELEMENT],

null

AS

[Student!2!Sdept!ELEMENT]FROM

StudentFOR

XML

EXPLICITSQL

Server发布XML文档-FORXMLEXPLICITSELECT

1

AS

TAG,

0

AS

PARENT,

Sno

AS

[Student!1!Sno],

Sname

AS

[Student!1!!ELEMENT],

Ssex

AS

[Student!1!Ssex!ELEMENT],

Sage

AS

[Student!1!Sage!ELEMENT],

Sdept

AS

[Student!1!Sdept!ELEMENT]FROM

StudentFOR

XML

EXPLICIT<StudentSno=2000012>

王林

<Ssex>男</Ssex>

<Sage>19</Sage>

<Sdept>计算机</Sdept></Student>SQL

Server发布XML文档-FORXMLEXPLICITwithaaaas(SELECT1ASTAG,0ASPARENT,SnoAS[Student!1!id],SnameAS[name!2!!ELEMENT],SsexAS[gender!3!!ELEMENT],SageAS[age!4!!ELEMENT],SdeptAS[department!5!!ELEMENT]FROMStudentUNIONALLSELECT2ASTAG,1ASPARENT,...FROMStudentUNIONALLSELECT3ASTAG,2ASPARENT,...FROMStudent)SELECT*FROMaaaORDERBY3FORXMLEXPLICITSQL

Server发布XML文档-FORXMLEXPLICITTAGPARENT[Student!1!id][name!2!!ELEMENT][gender!3!!ELEMENT]......102000012王林男19计算机212000012王林男19计算机322000012王林男19计算机102000014葛波女18计算机212000014葛波女18计算机322000014葛波女18计算机...<Studentid="2000012"><name