[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

[ 参考资料 ]

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=851182

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

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义