[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
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 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
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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
