SQL Server 2000 取得資料表 XML 結構描述的預存程序實作

需求場景

前端或中介層需將資料轉為 XML 格式傳給預存程序處理。若能先取得資料表的 XML 結構描述（Schema），即可定義對應的 XML 字串屬性與元素名稱，配合 OPENXML 函數輕鬆撰寫預存程序，無需手動對應欄位名稱。

解決方案：自訂預存程序 get_table_XML_structure

SQL Server 2000 無內建預存程序回傳 XML 結構，但可輕鬆自行開發。本方案查詢 INFORMATION_SCHEMA.COLUMNS 檢視（微軟推薦而非系統表）。

完整預存程序碼

CREATE PROCEDURE dbo.get_table_XML_structure 
    @table_name VARCHAR(255),
    @owner_name VARCHAR(30) = 'dbo'
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 暫存表儲存 XML 片段
    DECLARE @temp TABLE (string_value VARCHAR(4000));
    
    -- 驗證資料表是否存在
    IF NOT EXISTS (
        SELECT a.name
        FROM sysobjects a 
        INNER JOIN sysusers b ON a.uid = b.uid
        WHERE a.type = 'U' 
          AND a.name = @table_name 
          AND b.name = @owner_name
    )
    BEGIN
        RAISERROR('Incorrect table name specified, try again', 16, 1);
        RETURN;
    END
    
    -- 根元素開始標籤
    INSERT @temp SELECT '<' + @table_name + '>';
    
    -- 欄位元素（每欄一行，含開始/結束標籤）
    INSERT @temp
    SELECT '  <' + column_name + '> '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @table_name
      AND TABLE_SCHEMA = @owner_name
    ORDER BY ORDINAL_POSITION;
    
    -- 根元素結束標籤
    INSERT @temp SELECT '';
    
    -- 輸出結果
    SELECT string_value FROM @temp;
END;
GO

執行範例

EXEC get_table_XML_structure 'Orders', 'dbo';

輸出結果範例

<Orders>
  <OrderID> </OrderID>
  <CustomerID> </CustomerID>
  <EmployeeID> </EmployeeID>
  <OrderDate> </OrderDate>
  <RequiredDate> </RequiredDate>
  <ShippedDate> </ShippedDate>
  <ShipVia> </ShipVia>
  <Freight> </Freight>
  <ShipName> </ShipName>
  <ShipAddress> </ShipAddress>
  <ShipCity> </ShipCity>
  <ShipRegion> </ShipRegion>
  <ShipPostalCode> </ShipPostalCode>
  <ShipCountry> </ShipCountry>
</Orders>

進階應用：搭配 OPENXML 解析輸入 XML

CREATE PROCEDURE dbo.InsertOrderFromXML
    @xmlDoc TEXT
AS
BEGIN
    DECLARE @idoc INT;
    
    -- 準備 XML 文件
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc;
    
    -- 使用 OPENXML 解析並插入
    INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, ...)
    SELECT OrderID, CustomerID, EmployeeID, OrderDate, ...
    FROM OPENXML(@idoc, '/Orders', 2)
    WITH (
        OrderID INT 'OrderID',
        CustomerID NCHAR(5) 'CustomerID',
        EmployeeID INT 'EmployeeID',
        OrderDate DATETIME 'OrderDate',
        -- 其他欄位...
    );
    
    -- 釋放 XML 文件記憶體
    EXEC sp_xml_removedocument @idoc;
END;

關鍵優勢

• 動態適應結構變更： 資料表增減欄位時，XML 結構自動同步
• 減少硬式編碼： 前後端欄位對應由結構描述驅動
• 支援多擁有者： 透過 @owner_name 參數區分同名資料表

此技巧在 XML 資料交換、異構系統整合、動態表單處理等場景極具價值。