了解了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;
輸出結果如下:
如果在產品查詢子句中不提供TYPE指令,那麼輸出就會變成下面結果:
" />
以上幾個查詢結果都沒有帶命名空間,不過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');
輸出結果:
如果采用默認命名空間,編寫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');