[SQLXML]FOR XML语法导出XML的易错之处
时间:2022-03-14 04:07
原文:
Version
Date
Creator
Description
1.0.0.1
2006-6-29
郑昀@Ultrapower
草稿
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];否则可以直接跳过。
RAW
l AUTO
l EXPLICIT
这些模式仅在设置它们的查询执行时有效。它们对后面执行的任何查询的结果没有影响。除了指定 XML 模式外,还可以请求 XML-Data 架构。
Use pubs SELECT TOP 2 title_id, title, type FROM titles FOR XML AUTO, ELEMENTS 那么,输出结果就是:
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的语法为:
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 那么,输出结果就是:
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”,也就是表名。 很简单的语法。 但是如果数据量大的话,会发生什么事情呢? 比如我执行
Use pubs SELECT title_id, title, type FROM titles FOR XML AUTO, ELEMENTS 呢? 她还会返回一个完整的XML文档吗?
<?xml version="1.0" encoding=”GB2312” ?> <Shippers> <%begindetail%> <%insert_data_here%> <%enddetail%> </Shippers> 第二步,我们运行SQL语句:
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如下:
<?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数据流,本来需要你自己读,并且落地。现在,交给 而不再是一个记录。 这时候,有一个有趣的问题,可能XML的节点名也被一劈两半,分成两个记录。 这时候,如果你是用
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.
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