SQL Server 2000 XML 整合實戰：FOR XML 輸出與 OPENXML 輸入完整指南

SQL Server 2000 原生 XML 支援：雙向轉換完整解析

SQL Server 2000 內建強大 XML 功能：查詢直接輸出 XML、透過 XML 修改資料庫資料、將 XML 視為資料表來源。

一、查詢結果輸出為 XML：FOR XML 三種模式

1. FOR XML AUTO（最簡單、最常用）

SELECT TOP 1 ID, Filename, Download_Time, Introduce 
FROM download FOR XML AUTO

輸出：

• 表名 → 元素名
• 欄位名 → 屬性名
• 巢狀關係自動推導

2. FOR XML RAW（通用列元素）

SELECT TOP 1 ID, Filename, Download_Time, Introduce 
FROM download FOR XML RAW

輸出：

• 所有資料列統一用 <ROW> 元素
• 適合不需保留表結構語意的通用匯出

3. FOR XML AUTO, ELEMENTS（元素導向）

SELECT TOP 1 ID, Filename, Download_Time, Introduce 
FROM download FOR XML AUTO, ELEMENTS

輸出：

  1
  ayako_katagiri.exe
  1998-12-21T00:00:00
  片桐彩子Winamp Skin

• 欄位值以子元素而非屬性呈現
• 適合需支援複雜內容（含標記、巢狀結構）的場景

4. FOR XML EXPLICIT（完全控制，複雜度最高）

SELECT TOP 1 
    1 AS Tag, 
    NULL AS Parent,
    ID AS [DownloadXML!1!ID], 
    Filename AS [DownloadXML!1!Filename] 
FROM download FOR XML EXPLICIT

輸出：

• 需預先定義 Tag、Parent、欄位別名格式（ElementName!TagNumber!AttributeName）
• 功能最強大：可產生任意 XML 結構、混合屬性/元素、CDATA、命名空間
• 建議參閱線上叢書 Using EXPLICIT Mode

二、將 XML 作為資料表來源：OPENXML

OPENXML 可將 XML 文件解析為資料列集，像操作資料表一樣查詢。

完整範例：從 XML 字串解析下載清單

DECLARE @l INT
DECLARE @txtXML VARCHAR(1000)
SET @txtXML = '

  
  
  
  
  
  
  
  
  
  
'

EXEC sp_xml_preparedocument @l OUTPUT, @txtXML

SELECT * FROM OPENXML(@l, '/ROOT/DOWNLOAD', 1) 
WITH (ID INT, Filename VARCHAR(50))

EXEC sp_xml_removedocument @l

關鍵步驟解析：

1. sp_xml_preparedocument：解析 XML 字串，建立內部文件模型，回傳控制代碼
2. OPENXML(@handle, xpath, flags)：
   • @handle：文件代碼
   • xpath：節點路徑（/ROOT/DOWNLOAD）
   • flags：1=屬性導向，2=元素導向，3=混合
3. WITH 子句：定義結果集結構（欄位名、型別）
4. sp_xml_removedocument：**必須釋放記憶體**，否則洩漏

執行結果：

ID          Filename
----------- -----------------------------
1           ayako_katagiri.exe
2           beauti.mid
3           bee.mp3
4           behavior.js
5           cactivex.zip
6           chatsrvr.exe
7           chatter.exe
8           china.zip
9           ciyun.gb
10          ci_shanxi1.gb

三、XML 整合架構優勢

版本演進提示

• SQL Server 2000：關聯式引擎 + XML 視圖層（本文技術）
• SQL Server 2005+：原生 xml 資料型別、XQuery/XPath 原生支援、XML 索引
• 現代應用建議：直接使用 xml 型別與 XQuery，效能與功能全面優於 OPENXML