[SQLXML]FOR XML语法导出XML的易错之处-阿里云开发者社区

[SQLXML]FOR XML语法导出XML的易错之处

Version

Date

Creator

Description

1.0.0.1

2006-6-29

郑昀@Ultrapower

草稿

继续阅读之前，我们假设您熟悉以下知识：

n MS SQL Server 2000

n Sp_makewebtask

n FOR XML 子句

如果不熟悉这些知识点，可以看下面两小节[准备工作一：FOR XML]和[准备工作二：sp_makewebtask]；否则可以直接跳过。

[准备工作一：FOR XML]

关键词： FOR XML AUTO/EXPLICIT。

功能：可以对现有的关系数据库执行 SQL 查询，以返回 XML 文档形式而不是标准行集的结果。若要直接检索结果，请使用 SELECT 语句的 FOR XML 子句，并且在 FOR XML 子句中指定下列 XML 模式之一：

l RAW

l AUTO

l EXPLICIT

这些模式仅在设置它们的查询执行时有效。它们对后面执行的任何查询的结果没有影响。除了指定 XML 模式外，还可以请求 XML-Data 架构。

引申：在实际工作中，肯定会经常遇到要自己去查询数据库然后组织一个XML文档的需求，这时候就可以直接用FOR XML语法。

举一个最简单的例子：

Sql script

Use pubs

SELECT TOP 2 title_id, title, type

FROM titles FOR XML AUTO, ELEMENTS

那么，输出结果就是：

Sql result

XML_F52E2B61-18A1-11d1-B105-00805F49916B

-----------------------------------------------------------------------------------

<titles>

<title_id>BU1032</title_id>

<title>The Busy Executive's Database Guide</title>

<type>business </type>

</titles>

<titles>

<title_id>BU1111</title_id>

<title>Cooking with Computers: Surreptitious Balance Sheets</title>

<type>business </type>

</titles>

上面的例子，你自己并没有能够定义XML节点。下面用FOR XML EXPICIT就可以。

XML EXPLICIT的语法为：

[Element Tag!Tag!Attribute!Directive]

下面举一个例子：

Sql script

Use pubs

SELECT TOP 2

1 AS Tag,

NULL AS Parent,

title_id AS [titles!1!title_id],

title AS [titles!1!title!element],

type AS [titles!1!type]

FROM

titles

FOR XML EXPLICIT

那么，输出结果就是：

Sql result

XML_F52E2B61-18A1-11d1-B105-00805F49916B

-----------------------------------------------------------------------------------

<titles title_id="BU1032" type="business ">

<title>The Busy Executive's Database Guide</title>

</titles>

<titles title_id="BU1111" type="business ">

<title>Cooking with Computers: Surreptitious Balance Sheets</title>

</titles>

可以看到，

由于“[titles!1!title_id]”，所以titles节点有一个属性就是title_id；

由于“[titles!1!title!element]”，所以titles节点有一个子节点就是title；

之所以有“<titles>”节点，是因为“FROM titles”，也就是表名。

很简单的语法。

但是如果数据量大的话，会发生什么事情呢？

比如我执行

Sql script

Use pubs

SELECT title_id, title, type

FROM titles FOR XML AUTO, ELEMENTS

呢？

她还会返回一个完整的XML文档吗？

[准备工作二：sp_makewebtask]

关键词： sp_makewebtask。

功能：创建一项生成 HTML 文档的任务，该文档包含执行过的查询返回的数据。

引申：虽说这是一个SQL Server 2000用来根据查询结果来自动生成HTML文档的存储过程。但也还是经常被人用作输出XML文件的工具。

最简单的例子：

第一步，在C盘新建一个模板文件shippers_output_style.tpl，内容为：

template

<?xml version="1.0" encoding=”GB2312” ?>

<Shippers>

<%begindetail%>

<%insert_data_here%>

<%enddetail%>

</Shippers>

第二步，我们运行SQL语句：

Sql script

Use Northwind

EXEC sp_makewebtask

@outputfile = 'c:\Shippers.xml',

@query = 'SELECT * FROM Shippers FOR XML AUTO',

@templatefile ='c:\shippers_output_style.tpl'

第三步，文件已经生成，查看C盘的输出文件Shippers.xml如下：

Sql script

<?xml version="1.0" encoding=”GB2312”?>

<Shippers>

<Shippers ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831"/>

<Shippers ShipperID="2" CompanyName="United Package" Phone="(503) 555-3199" />

<Shippers ShipperID="3" CompanyName="Federal Shipping" Phone="(503) 555-9931" />

</Shippers>

也就是说，对于FOR XML语句生成的XML数据流，本来需要你自己读，并且落地。现在，交给sp_makewebtask这个系统存储过程即可。

它只不过需要特殊的权限才可以运行：sys_admin。

不过，sp_makewebtask强大定制功能还是不错的，它本身就提供自动定时生成功能。

同样，提一个问题，如果数据量很大，sp_makewebtask输出的FOR XML结果会是什么样呢？它还会是一个有效的XML文件吗？

[回答前面的问题]

如果查询结果数据量大的话，你可能会对你所看到的东西觉得奇怪。

假如你是在SQL Server2000的查询分析器里执行的SQL语句，那么你可能会看到折成好几个记录返回，如下所示：

而不再是一个记录。

这时候，有一个有趣的问题，可能XML的节点名也被一劈两半，分成两个记录。

这时候，如果你是用sp_makewebtask的自动生成文件功能，那么XML文件内容到处都是断裂的节点名，从而无法正常解析。

类似于

“

. ...</descriptio

n><pubDate>2009-06-27

”

Description节点名就被分裂为两块，中间还换了行，当然这个换行是因为我们的模板文件的“<%insert_data_here%>

<%enddetail%>”存在换行，但是如果因此调整为“<%insert_data_here%><%enddetail%>”，那么sp_makewebtask就不认endtail了，“<%enddetail%>”会原封不动出现在XML文件中，而没有做置换。

所以，即使你调整template模板文件内容也无济于事。这时候，解析XML的程序就会报告类似“结束标记 'body'与开始标记 'title' 不匹配”的错误。

为什么呢？因为sp_makewebtask的本身是为了生成HTML服务的，HTML可不在乎标签名断裂。

《SQL Server XML - Multiple rows returned by for xml explicit》提到了这个现象，并给出了解释。

[解释]

原因只是你用了“错误”的工具。

我试验过，不管是SQL Server 2000的查询分析器的“文本显示”/“表格显示”，还是SQL Server 2005的SQL Server Management Studio，或者是存储过程，或者是SQL Server 2000的作业，都无法避免这个问题。

但是，如果用dotNET中的XMLReader对象来读，就可以。

Rob自己也说：

The sql reader returns records and the xmlreader returns one xml. If you use the sqlreader you can concat the records and it will work but it is a waste to do it that way.

[可用的方法]

用下面的C#代码就可以保存一个完整的、没有被辟成几截的XML文件。注意，你的机器上必须安装SQL Server 2005安装盘下Servers\Setup\sqlxml4.msi，以拥有Microsoft.Data.SqlXml.DLL以及配套环境。

C# Codes

string coString = "Provider=sqloledb;data source=YourServer;user id=sa;password=;initial catalog=pubs";

SqlXmlCommand cmd = new SqlXmlCommand(coString);

XmlReader xr;

XmlDocument xDoc = new XmlDocument();

DataSet ds = new DataSet();

//Set the Root document tag

//to make sure the xml is well formed

cmd.RootTag = "Authors";

//set the clientSideXml property

cmd.ClientSideXml = true;

//call the existing strored proc

//and append the for xml nested syntax

cmd.CommandText = "exec proc_output_authors";

//Execute the reader

xr = cmd.ExecuteXmlReader();

//load the xml document with

//the contents of the reader

xDoc.Load(xr);

//Persist the document to disk

xDoc.Save(txtXMLFilePath.Text);

那边的存储过程实际就是这样的语句：

/* Body of XML Document */

select

Author.au_fname as FirstName,

Author.au_lname as LastName,

Book.title as BookTitle,

Book.title_id as BookId

from

authors as Author

inner join

dbo.titleauthor as Titles

Author.au_id = Titles.au_id

inner join

dbo.titles as Book

Titles.title_id = Book.title_id

for

xml auto

[SQLXML]FOR XML语法导出XML的易错之处