SQL Server 2005 FOR XML 進階：TYPE 指令、XML 資料型別與巢狀查詢 (上)

SQL Server 2005 大幅增強 FOR XML 功能：支援 xml 資料型別、PATH 模式、巢狀 FOR XML 查詢，能產出結構更靈活的 XML。

TYPE 指令：回傳 xml 型別而非字串

-- 無 TYPE：回傳 nvarchar 字串
DECLARE @TestString NVARCHAR(1024);
SET @TestString = (SELECT ProductID, [Name] FROM Production.Product
                   WHERE ProductSubcategoryID = 9
                   FOR XML AUTO);
SELECT @TestString;  -- 無法在 SSMS 點擊檢視 XML

-- 加上 TYPE：回傳 xml 型別
DECLARE @XmlTest XML;
SET @XmlTest = (SELECT ProductID, [Name] FROM Production.Product
                WHERE ProductSubcategoryID = 9
                FOR XML AUTO, TYPE);
SELECT @XmlTest;  -- SSMS 可點擊檢視結構化 XML

關鍵差異：TYPE 讓結果成為 xml 型別，支援 XQuery、索引、結構化檢視；若指派給字串變數會報錯，需用 CONVERT 或直接宣告 xml 變數。

巢狀 FOR XML 查詢：一對多關係單次查詢產出階層 XML

SELECT ProductSubcategoryID, [Name],
       (SELECT ProductID, [Name] FROM Production.Product Product
        WHERE Product.ProductSubcategoryID = 9
        FOR XML AUTO, TYPE) AS Products
FROM Production.ProductSubcategory AS Category
WHERE ProductSubcategoryID = 9
FOR XML AUTO;

子查詢加 TYPE 產出 xml 型別片段，外層自動內嵌為子元素。若子查詢無 TYPE，結果會被實體化為字串 (出現 < > 逸出字元)。

命名空間支援：WITH XMLNAMESPACES

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');

或使用預設命名空間：

WITH XMLNAMESPACES (DEFAULT 'https://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');

下篇將介紹 PATH 模式與 .NET/BizTalk 整合實務。