用户操作
[留言]  [发消息]  [加为好友] 
订阅我的博客
XML聚合    FeedSky
订阅到鲜果
订阅到Google
订阅到抓虾
zhzuo的公告
本站内容只是<a href="https://mvp.support.microsoft.com/profile=e190fdb5-f53c-473b-9564-fab6a507dfd1" target="_blank"><span style="color: red" >作者</span></a>某一时期的观点，对于其正确性没有任何担保或假设。 <a rel="license" href="http://creativecommons.org/licenses/by-nc-sa/2.5/" target="_blank"><img alt="Creative Commons License" style="border-width:0" src="http://i.creativecommons.org/l/by-nc-sa/2.5/80x15.png" /></a>
文章分类
资源
存档

原创  使用SQL Server 2005 FOR XML嵌套查询 收藏

 
使用SQL Server 2005 FOR XML嵌套查询
日期：2008-06-08
作者：郑佐
相比SQL Server 2000提供的FOR XML查询，SQL Server 2005版本对现有功能增强的基础上增加了不少新功能，最为吸引人的功能包括对Xml数据类型支持、使用PATH模式以及嵌套FOR XML查询支持等，这意味着通过新的FOR XML查询功能可以构造出结构更加灵活的Xml数据。
在SQL Server 2000中FOR XML查询的结果是直接以文本方式返回到客户端，为支持SQL Server 2005中的Xml数据类型，FOR XML 查询提供了TYPE指令，通过TYPE支持返回结果将会变为Xml数据类型。接下来以SQL Server2005中的AdventureWorks示例数据库为例演练其功能。
先来看第一个SQL查询：
DECLARE @TestStringValue nvarchar(1024);
SET @TestStringValue = (SELECT ProductID,[Name] FROM Production.Product
WHERE ProductSubcategoryID = 9
FOR XML AUTO);
SELECT @TestStringValue;
输出结果：
<Production.Product ProductID="894" Name="Rear Derailleur"/><Production.Product ProductID="945" Name="Front Derailleur"/>
结果为字符串，只是内容格式符合Xml片段要求，不过无法在SQL Server Management Studio中点击以Xml方式显示。
下面对SQL进行修改，增加TYPE指令：
DECLARE @TestStringValue nvarchar(1024);
SET @TestStringValue = (SELECT ProductID,[Name] FROM Production.Product
WHERE ProductSubcategoryID = 9
FOR XML AUTO,TYPE);
不出意外，查询结果收到如下错误提示：
不允许从数据类型xml 到nvarchar 的隐式转换。请使用CONVERT 函数来运行此查询。
可见通过TYPE指令返回的数据已经转变成Xml类型，改进后的查询语句如下：
DECLARE @XmlTestValue xml;
SET @XmlTestValue = (SELECT ProductID,[Name] FROM Production.Product
WHERE ProductSubcategoryID = 9
FOR XML AUTO,TYPE);
SELECT @XmlTestValue;
通过SELECT 返回的结果支持Xml方式显示。
 
了解了TYPE指令的功能后，就可以来看FOR XML查询带给开发者的更多惊喜。以示例数据库的产品表（Production.Product）和产品分类子类表（Production.ProductSubcategory）为例进行演示。
编写产品子类别为“9”的产品数据查询：
SELECT ProductSubcategoryID,[Name] ,
(SELECT ProductID,[Name] FROM Production.Product Product
    WHERE ProductSubcategoryID = 9
    FOR XML AUTO,TYPE) AS Products
FROM Production.ProductSubcategory AS Category
WHERE ProductSubcategoryID = 9
FOR XML AUTO;
输出结果如下：
<CategoryProductSubcategoryID="9"Name="Derailleurs">
 <Products>
    <ProductProductID="894"Name="Rear Derailleur" />
    <ProductProductID="945"Name="Front Derailleur" />
 </Products>
</Category>
如果在产品查询子句中不提供TYPE指令，那么输出就会变成下面结果：
<CategoryProductSubcategoryID="9"Name="Derailleurs"Products="&lt;Product ProductID=&quot;894&quot; Name=&quot;Rear Derailleur&quot;/&gt;&lt;Product ProductID=&quot;945&quot; Name=&quot;Front Derailleur&quot;/&gt;" />
 
以上几个查询结果都没有带命名空间，不过SQL Server 2005 通过WITH XMLNAMESPACES子句提供了对命名空间的支持，下面的查询将显示一个完整的带默认命名空间的Xml文档。
WITH XMLNAMESPACES ('uri0' as ns0)
SELECT ProductSubcategoryID,[Name] ,
(SELECT ProductID,[Name] FROM Production.Product Product
    WHERE ProductSubcategoryID = 9
    FOR XML RAW('ns0:Product'),TYPE) AS 'ns0:Products'
FROM Production.ProductSubcategory AS Category
WHERE ProductSubcategoryID = 9
FOR XML RAW('ns0:Category'),ROOT('ns0:Message');
输出结果：
<ns0:Messagexmlns:ns0="uri0">
 <ns0:CategoryProductSubcategoryID="9"Name="Derailleurs">
    <ns0:Products>
      <ns0:Productxmlns:ns0="uri0"ProductID="894"Name="Rear Derailleur" />
      <ns0:Productxmlns:ns0="uri0"ProductID="945"Name="Front Derailleur" />
    </ns0:Products>
 </ns0:Category>
</ns0:Message>
 
如果采用默认命名空间，编写SQL如下：
WITH XMLNAMESPACES (DEFAULT 'http://blog.csdn.net/zhzuo')
SELECT ProductSubcategoryID,[Name] ,
(SELECT ProductID,[Name] FROM Production.Product Product
    WHERE ProductSubcategoryID = 9
    FOR XML AUTO,TYPE) AS Products
FROM Production.ProductSubcategory AS Category
WHERE ProductSubcategoryID = 9
FOR XML AUTO,ROOT('Message');
输出结果：
<Messagexmlns="http://blog.csdn.net/zhzuo">
 <CategoryProductSubcategoryID="9"Name="Derailleurs">
    <Products>
      <Productxmlns="http://blog.csdn.net/zhzuo"ProductID="894"Name="Rear Derailleur" />
      <Productxmlns="http://blog.csdn.net/zhzuo"ProductID="945"Name="Front Derailleur" />
    </Products>
 </Category>
</Message>
 
另外，在SQL Server 2005中增加了FOR XML PATH 模式，通过使用FOR XML PATH 模式可以建立清晰灵活的Xml格式，相比使用FOR XML EXPLICIT更为简单：
WITH XMLNAMESPACES (DEFAULT 'http://blog.csdn.net/zhzuo')
SELECT ProductID AS 'Product/@ProductID',
    Name AS 'Product/Name'
FROM Production.Product
WHERE ProductSubcategoryID = 9
FOR XML PATH(''),ROOT('Products');
输出结果：
<Productsxmlns="http://blog.csdn.net/zhzuo">
 <ProductProductID="894">
    <Name>Rear Derailleur</Name>
 </Product>
 <ProductProductID="945">
    <Name>Front Derailleur</Name>
 </Product>
</Products>
 
在实际应用过程中，以.NET平台应用开发为例，通过ADO.NET访问SQL Server 2005返回的Xml数据结构可以更加丰富，甚至可以做到不做Xml样式转换直接服务于上一层模块。在企业应用集成方面，以BizTalk平台为例，在调用SQL Adapter的时候可以更加灵活，降低为了制定合理Xml Schema带来的复杂性。目前，最新版本的BizTalk（2006 R2）对使用了SQL Server 2005加强FOR XML功能的存储过程或SQL语句不支持通过SQL适配器向导生成XML Schema，不过这个问题可以直接通过Visual Studio IDE编写符合实际格式的XML Schema来解决。

发表于 @ 2008年06月08日　22:08:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:BizTalk Adapter for FTP访问小型机问题及处理 | 新一篇:使用BackgroundWorker组件进行异步操作编程
查看最新精华文章 请访问博客首页相关文章
  • 发表评论
  • 评论内容：
  •  
Copyright © zhzuo
Powered by CSDN Blog