[SQLXML]FOR XML 語(yǔ)法導(dǎo)出 XML 的易錯(cuò)之處
|
Version
|
Date
|
Creator
|
Description
|
|
1.0.0.1
|
2006-6-29
|
鄭昀
@Ultrapower
|
草稿
|
繼續(xù)閱讀之前，我們假設(shè)您熟悉以下知識(shí)：
n
????????
MS SQL Server 2000
n
????????
Sp_makewebtask
n
????????
FOR XML
子句
如果不熟悉這些知識(shí)點(diǎn)，可以看下面兩小節(jié)
[
準(zhǔn)備工作一：
FOR XML]
和
[
準(zhǔn)備工作二：
sp_makewebtask]
；否則可以直接跳過(guò)。
[ 準(zhǔn)備工作一： FOR XML]
關(guān)鍵詞
：
?????
FOR XML AUTO/EXPLICIT
。
功能 ： ????????? 可以對(duì)現(xiàn)有的關(guān)系數(shù)據(jù)庫(kù)執(zhí)行 SQL 查詢(xún)，以返回 XML 文檔形式而不是標(biāo)準(zhǔn)行集的結(jié)果。若要直接檢索結(jié)果，請(qǐng)使用 SELECT 語(yǔ)句的 FOR XML 子句，并且在 FOR XML 子句中指定下列 XML 模式之一：
l ???????? RAW
l ???????? AUTO
l ???????? EXPLICIT
這些模式僅在設(shè)置它們的查詢(xún)執(zhí)行時(shí)有效。它們對(duì)后面執(zhí)行的任何查詢(xún)的結(jié)果沒(méi)有影響。除了指定 XML 模式外，還可以請(qǐng)求 XML-Data 架構(gòu)。
引申 ： ????????? 在實(shí)際工作中，肯定會(huì)經(jīng)常遇到要自己去查詢(xún)數(shù)據(jù)庫(kù)然后組織一個(gè) XML 文檔的需求，這時(shí)候就可以直接用 FOR XML 語(yǔ)法。
舉一個(gè)最簡(jiǎn)單的例子：
|
Sql script
|
|
Use pubs
SELECT TOP 2 title_id, title, type
??????
FROM titles FOR XML AUTO, ELEMENTS
|
那么，輸出結(jié)果就是：
|
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>
|
上面的例子，你自己并沒(méi)有能夠定義 XML 節(jié)點(diǎn)。下面用 FOR XML EXPICIT 就可以。
XML EXPLICIT 的語(yǔ)法為：
[Element Tag!Tag!Attribute!Directive]
下面舉一個(gè)例子：
|
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
|
那么，輸出結(jié)果就是：
|
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 節(jié)點(diǎn)有一個(gè)屬性就是 title_id ；
由于“ [titles!1!title!element] ”，所以 titles 節(jié)點(diǎn)有一個(gè)子節(jié)點(diǎn)就是 title ；
之所以有“ <titles> ”節(jié)點(diǎn)，是因?yàn)椤? FROM titles ”，也就是表名。
很簡(jiǎn)單的語(yǔ)法。
但是如果數(shù)據(jù)量大的話，會(huì)發(fā)生什么事情呢？
比如我執(zhí)行
|
Sql script
|
|
Use pubs
SELECT title_id, title, type
??????
FROM titles FOR XML AUTO, ELEMENTS
|
呢？
她還會(huì)返回一個(gè)完整的
XML
文檔嗎？
[ 準(zhǔn)備工作二： sp_makewebtask]
關(guān)鍵詞：
??????
sp_makewebtask
。
功能： ?????????? 創(chuàng)建一項(xiàng)生成 HTML 文檔的任務(wù)，該文檔包含執(zhí)行過(guò)的查詢(xún)返回的數(shù)據(jù)。
引申： ?????????? 雖說(shuō)這是一個(gè) SQL Server 2000 用來(lái)根據(jù)查詢(xún)結(jié)果來(lái)自動(dòng)生成 HTML 文檔的存儲(chǔ)過(guò)程。但也還是經(jīng)常被人用作輸出 XML 文件的工具。
最簡(jiǎn)單的例子：
第一步，在 C 盤(pán)新建一個(gè)模板文件 shippers_output_style.tpl ，內(nèi)容為：
|
template
|
|
<?xml version="1.0" encoding=”GB2312” ?>
<Shippers>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</Shippers>
|
第二步，我們運(yùn)行 SQL 語(yǔ)句：
|
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'
|
第三步，文件已經(jīng)生成，查看
C
盤(pán)的輸出文件
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>
|
也就是說(shuō)，對(duì)于
FOR XML
語(yǔ)句生成的
XML
數(shù)據(jù)流，本來(lái)需要你自己讀，并且落地。現(xiàn)在，交給
sp_makewebtask
這個(gè)系統(tǒng)存儲(chǔ)過(guò)程
即可。
它只不過(guò)需要特殊的權(quán)限才可以運(yùn)行：
sys_admin
。
不過(guò)，
sp_makewebtask
強(qiáng)大定制功能還是不錯(cuò)的，它本身就提供自動(dòng)定時(shí)生成功能。
同樣，提一個(gè)問(wèn)題，如果數(shù)據(jù)量很大，
sp_makewebtask
輸出的
FOR XML
結(jié)果會(huì)是什么樣呢？它還會(huì)是一個(gè)有效的
XML
文件嗎？
[ 回答前面的問(wèn)題 ]
如果查詢(xún)結(jié)果數(shù)據(jù)量大的話，你可能會(huì)對(duì)你所看到的東西覺(jué)得奇怪。
假如你是在 SQL Server2000 的查詢(xún)分析器里執(zhí)行的 SQL 語(yǔ)句，那么你可能會(huì)看到折成好幾個(gè)記錄返回，如下所示：
而不再是一個(gè)記錄。
這時(shí)候，有一個(gè)有趣的問(wèn)題，可能
XML
的節(jié)點(diǎn)名也被一劈兩半，分成兩個(gè)記錄。
這時(shí)候，如果你是用
sp_makewebtask
的自動(dòng)生成文件功能，那么
XML
文件內(nèi)容到處都是斷裂的節(jié)點(diǎn)名，從而無(wú)法正常解析。
類(lèi)似于
“
. ...</descriptio
n><pubDate>2009-06-27
”
Description
節(jié)點(diǎn)名就被分裂為兩塊，中間還換了行，當(dāng)然這個(gè)換行是因?yàn)槲覀兊哪０逦募摹?
<%insert_data_here%>
<%enddetail%>
”存在換行，但是如果因此調(diào)整為“
<%insert_data_here%><%enddetail%>
”，那么
sp_makewebtask
就不認(rèn)
endtail
了，“
<%enddetail%>
”會(huì)原封不動(dòng)出現(xiàn)在
XML
文件中，而沒(méi)有做置換。
所以，即使你調(diào)整
template
模板文件內(nèi)容也無(wú)濟(jì)于事。這時(shí)候，解析
XML
的程序就會(huì)報(bào)告類(lèi)似“
結(jié)束標(biāo)記
'body'
與開(kāi)始標(biāo)記
'title'
不匹配
”的錯(cuò)誤。
為什么呢？因?yàn)?
sp_makewebtask
的本身是為了生成
HTML
服務(wù)的，
HTML
可不在乎標(biāo)簽名斷裂。
《
SQL Server XML - Multiple rows returned by for xml explicit
》提到了這個(gè)現(xiàn)象，并給出了解釋。
[ 解釋 ]
原因只是你用了“錯(cuò)誤”的工具。
我試驗(yàn)過(guò)，不管是
SQL Server 2000
的查詢(xún)分析器的“文本顯示”
/
“表格顯示”，還是
SQL Server 2005
的
SQL Server Management Studio
，或者是存儲(chǔ)過(guò)程，或者是
SQL Server 2000
的作業(yè)，都無(wú)法避免這個(gè)問(wèn)題。
但是，如果用
dotNET
中的
XMLReader
對(duì)象來(lái)讀，就可以。
Rob
自己也說(shuō)：
|
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#
代碼就可以保存一個(gè)完整的、沒(méi)有被辟成幾截的
XML
文件。注意，你的機(jī)器上必須安裝
SQL Server 2005
安裝盤(pán)下
Servers\Setup\sqlxml4.msi
，以擁有
Microsoft.Data.SqlXml.DLL
以及配套環(huán)境。
|
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);
|
?????? 那邊的存儲(chǔ)過(guò)程實(shí)際就是這樣的語(yǔ)句：
/* 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
[
參考資料
]
-
Understanding EXPLICIT XML Formatting
-
Understanding and Using SELECT ... FOR XML
-
SQL Server XML - Multiple rows returned by for xml explicit
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=851182
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
