SQL Server 2000 建立自訂 XML 資料流：游標迴圈組合 FOR XML AUTO ELEMENTS 產生巢狀結構

應用場景

需產生巢狀 XML 結構：主表 (員工) → 子表集合 (電話、其他資訊)，對應 XML 模型：

<ROOT>
  <DATA>
    <LNAME/><FNAME/>
    <PHONE><PHONE_TYPE/><PHONE_NO/></PHONE> (多筆)
    <OTHER_INFO><INFO_TYPE/><INFO_DATA/></OTHER_INFO> (多筆)
  </DATA>
</ROOT>

單一 FOR XML AUTO 限制

多表 JOIN 產生的巢狀層級難以精確控制，尤其一對多關聯會產生重複父節點資料，不符合預期階層。

解決方案：預存程序 + 游標 + 多重 FOR XML 組合

CREATE PROC usp_GetEmployeePhoneXml
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @emp_id INT, @fname VARCHAR(50), @lname VARCHAR(50);
    DECLARE cur CURSOR FOR 
        SELECT EmployeeID, FirstName, LastName FROM Employees ORDER BY LastName, FirstName;

    OPEN cur;
    FETCH NEXT FROM cur INTO @emp_id, @fname, @lname;

    -- 輸出根元素開始 (由呼叫端控制或此處輸出)
    -- PRINT '';  -- 若在預存程序輸出，需協調呼叫端

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 1. 輸出員工基本節點
        PRINT '' + @fname + '' + @lname + '';

        -- 2. 電話資訊 (FOR XML AUTO, ELEMENTS 產生子元素而非屬性)
        SELECT PhoneType, PhoneNo 
        FROM Phones 
        WHERE EmployeeID = @emp_id 
        FOR XML AUTO, ELEMENTS;

        -- 3. 其他資訊
        SELECT InfoType, InfoData 
        FROM OtherInfo 
        WHERE EmployeeID = @emp_id 
        FOR XML AUTO, ELEMENTS;

        -- 4. 關閉員工節點
        PRINT '';

        FETCH NEXT FROM cur INTO @emp_id, @fname, @lname;
    END

    -- PRINT '';
    CLOSE cur; DEALLOCATE cur;
    SET NOCOUNT OFF;
END
GO

ADO Stream 接收預存程序 XML 輸出

<XML id="xmlPhoneData">
<%
Dim adoConn, adoCmd, adoStream
Set adoConn = Server.CreateObject("ADODB.Connection")
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoStream = Server.CreateObject("ADODB.Stream")

adoConn.ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;User Id=sa;Password=pwd;"
adoConn.Open
Set adoCmd.ActiveConnection = adoConn
adoCmd.CommandType = 4 ' adCmdStoredProc
adoCmd.CommandText = "usp_GetEmployeePhoneXml"

adoStream.Open
Set adoCmd.Properties("Output Stream") = adoStream
adoCmd.Execute ,, 1024 ' adExecuteStream

Response.Write adoStream.ReadText(-1)

adoStream.Close
Set adoStream = Nothing
adoConn.Close
Set adoCmd = Nothing
Set adoConn = Nothing
%>
</XML>

關鍵技術點

• SET NOCOUNT ON：抑制「影響 n 筆資料」訊息干擾 XML 輸出。
• PRINT 陳述句輸出靜態 XML 片段 (父節點標籤)。
• FOR XML AUTO, ELEMENTS：強制欄位輸出為子元素 (<PhoneType>Mobile</PhoneType>)，而非屬性 (PhoneType="Mobile")，符合文件導向 XML 結構。
• 游標效能較差，預存程序預編譯緩解；大資料量建議改用 FOR XML PATH (SQL 2005+) 或應用層組裝。
• 輸出編碼為 Unicode (UTF-16)，前端顯示需正確宣告 <?xml encoding="utf-16"?> 或轉碼。

現代替代方案 (SQL Server 2005+)

SELECT 
    e.LastName AS 'DATA/LNAME',
    e.FirstName AS 'DATA/FNAME',
    (SELECT PhoneType, PhoneNo 
     FROM Phones p WHERE p.EmployeeID = e.EmployeeID 
     FOR XML PATH('PHONE'), TYPE, ELEMENTS),
    (SELECT InfoType, InfoData 
     FROM OtherInfo o WHERE o.EmployeeID = e.EmployeeID 
     FOR XML PATH('OTHER_INFO'), TYPE, ELEMENTS)
FROM Employees e
FOR XML PATH(''), ROOT('ROOT'), ELEMENTS;

FOR XML PATH 可聲明式構建任意巢狀結構，無需游標，效能與可維護性大幅優於 SQL 2000 方案。