SQL Server 2000 XML 集成實戰：FOR XML 三種模式、HTTP 端點配置、模板查詢與參數化

FOR XML 三種模式

語法速查

SELECT * FROM table1
[FOR XML {RAW | AUTO | EXPLICIT} [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]]
[OPTION (<query hint>...)]

• ELEMENTS：列作子元素而非屬性
• XMLDATA：返回 XDR Schema（已棄用，改用 XSD）
• BINARY BASE64：二進制列 Base64 編碼

EXPLICIT 模式構建範例

SELECT 1 AS Tag, NULL AS Parent,
       Employees.LastName AS [Employees!1!LastName],
       NULL AS [EmployeeTerritories!2!TerritoryID]
FROM Employees
UNION ALL
SELECT 2, 1,
       Employees.LastName,
       EmployeeTerritories.TerritoryID
FROM EmployeeTerritories
JOIN Employees ON EmployeeTerritories.EmployeeID = Employees.EmployeeID
ORDER BY [Employees!1!LastName], Tag
FOR XML EXPLICIT;

關鍵規則：

1. 通用表必須包含 Tag（當前層級標識）和 Parent（父層級 Tag）
2. 列名格式：[元素名!Tag值!列名]
3. ORDER BY 必須包含父元素排序鍵 + Tag，保證父子相鄰

HTTP 端點配置（IIS 虛擬目錄）

1. 運行「在 IIS 中配置 SQL XML 支持」
2. 默認網站 → 新建虛擬目錄（如 sqlxml）
3. 常規：指定本地路徑（存放模板文件）
4. 安全性：輸入 SQL 登錄賬號（建議低權限專用賬號）
5. 數據源：選擇目標數據庫（如 Northwind）
6. 設置：勾選「允許 URL 查詢」「允許模板查詢」
7. 虛擬名稱：新建 schema（URL 查詢）、template（模板）、dbobject（對象）目錄

URL 直接查詢

https://localhost/sqlxml?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO&root=root

• sql= 後接 URL 編碼的 SQL（空格用 +）
• root= 指定根元素名

模板查詢（推薦生產使用）

文件 template/getCustomers.xml：

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:query>
    SELECT * FROM Customers FOR XML AUTO
  </sql:query>
</root>

訪問：https://localhost/sqlxml/template/getCustomers.xml

參數化模板

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:header>
    <sql:param name="type">U</sql:param>  <!-- 默認值 -->
  </sql:header>
  <sql:query>
    SELECT xtype, name FROM sysobjects WHERE xtype = @type FOR XML AUTO
  </sql:query>
</root>

訪問：https://localhost/sqlxml/template/getObjects.xml?type=P

安全性最佳實踐

• 虛擬目錄賬號：最小權限，僅 SELECT / EXEC 業務存儲過程
• 禁用 xp_cmdshell 等危險擴展存儲過程
• 生產環境僅開放模板查詢，關閉 URL 直接查詢
• IIS 層面啟用 SSL、IP 限制、速率限制
• 模板文件存放於 Web 根目錄外部，防止直接下載

適用場景

• 異構系統集成（HTTP+XML 通用）
• 報表服務數據源
• 移動端/前端直接消費 XML
• 避免部署中間層的輕量級數據服務