SQL Server OPENXML 完整指南：XML 轉關聯表實戰詳解

應用場景

將階層式 XML 資料「平展」為二維關聯表，便於 SQL 查詢與分析。本文以廣告追蹤資料為例，示範完整流程。

範例 XML 結構

<?XML version="1.0"?>
<Result>
  <ad id="3471">
    <Item Time="953" Frames="122" TimeStamp="2007-09-21 14:21:27" AvgArea="53.577499" />
  </ad>
  <ad id="3472">
    <Item Time="20281" Frames="1899" TimeStamp="2007-09-21 14:20:03" AvgArea="99.947342" />
  </ad>
  ...
</Result>

邏輯結構：Result 根節點 → 多個 ad 節點（含 id 屬性）→ 每個 ad 下含多個 Item 節點。

目標表結構

CREATE TABLE T (
    ADID      VARCHAR(10),   -- ad.id
    ShowTimes INT,           -- Item.Time
    Frames    INT,           -- Item.Frames
    StatTime  DATETIME,      -- Item.TimeStamp
    AvgArea   DECIMAL(20,4)  -- Item.AvgArea
);

核心腳本

-- 1. 準備 XML 文件
DECLARE @idoc INT;
EXEC sp_xml_preparedocument @idoc OUTPUT, '<Result>...</Result>';

-- 2. 使用 OPENXML 查詢
SELECT *
FROM OPENXML (@idoc, '/Result/ad/Item', 1)
WITH (
    ADID      VARCHAR(10)  '../@id',
    ShowTimes INT          '@Time',
    Frames    INT          '@Frames',
    StatTime  DATETIME     '@TimeStamp',
    AvgArea   DECIMAL(20,4) '@AvgArea'
);

-- 3. 釋放資源
EXEC sp_xml_removedocument @idoc;

關鍵概念解析

映射旗標

可組合使用（如 3 = 1+2）。若 WITH 子句路徑完整，旗標實際無影響。

ColPattern 路徑描述

• ../@id：.. 表父層節點，@ 表屬性
• @Time：目前節點的 Time 屬性
• 無前綴：對應元素值
• @mp:???：中繼屬性（如 @mp:id、@mp:parentid）

省略 WITH 子句取得邊緣表

SELECT * FROM OPENXML (@idoc, 'A/B', 0);

可取得所有中繼屬性欄位：@mp:id、@mp:parentid、@mp:localname、@mp:namespaceuri 等。

實務建議

• 命名慣例：避免單字母、表名全大寫、欄位 PascalCase、變數 camelCase
• 完整指定 ColPattern 可避免映射旗標歧義
• 大量資料考慮效能，OPENXML 適合中小型 XML