开发者社区> 杰克.陈> 正文

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

简介: 原文:[SQLXML]FOR XML语法导出XML的易错之处  [SQLXML]FOR XML语法导出XML的易错之处 Version Date ...
+关注继续查看
原文:[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&apos;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&apos;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

GO

 

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语句,那么你可能会看到折成好几个记录返回,如下所示:

o_multiRowsOfFORXMLAUTO.JPG

而不再是一个记录。

这时候,有一个有趣的问题,可能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 2005SQL 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

on

       Author.au_id = Titles.au_id

inner join

       dbo.titles as Book

on

       Titles.title_id = Book.title_id

for

       xml auto

 

[参考资料]

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
19676 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
14671 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
17023 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
18607 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载