FOR XML语法导出XML的易错之处

原文地址:http://www.cnblogs.com/zhengyun_ustc/archive/2006/06/29/sqlxml_forxmlexplicit.html

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

而不再是一个记录。

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

 

[参考资料]

展开阅读全文

MySQL数据库从入门到实战应用

12-31
限时福利1:购课进答疑群专享柳峰(刘运强)老师答疑服务 限时福利2:购课后添加学习助手(微信号:csdn590),按消息提示即可领取编程大礼包! 为什么说每一个程序员都应该学习MySQL? 根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。 使用量大同时,掌握MySQL早已是运维、DBA的必备技能,甚至部分IT开发岗位也要求对数据库使用和原理有深入的了解和掌握。 学习编程,你可能会犹豫选择 C++ 还是 Java;入门数据科学,你可能会纠结于选择 Python 还是 R;但无论如何, MySQL 都是 IT 从业人员不可或缺的技能! 【课程设计】 在本课程中,刘运强老师会结合自己十多年来对MySQL的心得体会,通过课程给你分享一条高效的MySQL入门捷径,让学员少走弯路,彻底搞懂MySQL。 本课程包含3大模块:  一、基础篇: 主要以最新的MySQL8.0安装为例帮助学员解决安装与配置MySQL的问题,并对MySQL8.0的新特性做一定介绍,为后续的课程展开做好环境部署。 二、SQL语言篇: 本篇主要讲解SQL语言的四大部分数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL,学会熟练对库表进行增删改查等必备技能。 三、MySQL进阶篇: 本篇可以帮助学员更加高效的管理线上的MySQL数据库;具备MySQL的日常运维能力,语句调优、备份恢复等思路。  
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值