开发者社区> 郑昀> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

[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

 

[参考资料]

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

相关文章
Java Web之JSP操作XML(XML的文档结构 语法和注释、dom4j的下载与配置 应用dom4j创建、解析和修改XML)
Java Web之JSP操作XML(XML的文档结构 语法和注释、dom4j的下载与配置 应用dom4j创建、解析和修改XML)
0 0
xml语法中<![CDATA[ ]]>的用法
<![CDATA[ ]]> 是什么,这是XML语法。特别是在mybatis中的mapper映射里会经常使用到该模块,具体点来说,当编译时xml文件时,在CDATA内部的所有内容都会被解析器忽略,
0 0
XML教程、语法手册、数据读取方式大全
XML简单易懂教程 本文提供全流程,中文翻译。Chinar坚持将简单的生活方式,带给世人!(拥有更好的阅读体验 —— 高分辨率用户请根据需求调整网页缩放比例) 一 XML ——数据格式的写...
841 0
XML DTD语法详解
XML DTD详解     XML DTD详解   前情提要与本文内容介绍   前面的两篇XML相关博文:   第一篇是介绍格式正规的XML: 格式正规的XML:语法 属性 实体 处理指令 样式单 CDATA节   第二篇介绍DTD,引入有效的XML的概念(符合语法规范并符合DTD要求),并介绍DTD的三种使用方法:有效的XML: DTD(文档类型定义)介绍与三种使用方式   一个有效的XML文档必然是结构正规的,结构正规的XML文档不一定是有效的,即有效的是格式正规的一个子集。
845 0
[SQLXML]FOR XML语法导出XML的易错之处
原文:[SQLXML]FOR XML语法导出XML的易错之处  [SQLXML]FOR XML语法导出XML的易错之处 Version Date ...
660 0
XDocument读取xml的所有元素以及XPath语法
原文 http://www.cnblogs.com/xxyishutong/archive/2013/09/17/3326375.html Everyday Italian Giada De Laurentiis 2005 30.
1016 0
xml 解析技术介绍和解析xml文件
xml 解析技术介绍和解析xml文件
0 0
+关注
郑昀
☑移动数据业务&times;6年 ☑语义聚合&times;4年 ☑O2O&times;5年的一个老兵。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载