一、XML 匯入資料表 (OPENXML / OPENROWSET)
1. 字串變數 → 表 (單表)
DECLARE @s NVARCHAR(MAX) = N'';
DECLARE @h INT;
EXEC sp_xml_preparedocument @h OUTPUT, @s;
INSERT INTO Xmltable(Name, Nowtime)
SELECT * FROM OPENXML(@h, N'/Xmltables/Xmltable') WITH Xmltable;
EXEC sp_xml_removedocument @h;
2. 檔案 → 表 (單表)
DECLARE @h INT, @x XML;
SELECT @x = BulkColumn FROM OPENROWSET(BULK 'E:\xml.xml', SINGLE_BLOB) x;
EXEC sp_xml_preparedocument @h OUTPUT, @x;
SELECT * INTO #tmp FROM OPENXML(@h, N'/root/dbo.Xmltable') WITH (Name NVARCHAR(20), Intro NVARCHAR(20));
EXEC sp_xml_removedocument @h;
3. 多表同時匯入
DECLARE @s NVARCHAR(MAX) = N'';
DECLARE @h INT;
EXEC sp_xml_preparedocument @h OUTPUT, @s;
INSERT INTO Xmlta(Name) SELECT * FROM OPENXML(@h, N'/Xmltables/Xmlta') WITH Xmlta;
INSERT INTO Xmltb(Name) SELECT * FROM OPENXML(@h, N'/Xmltables/Xmltb') WITH Xmltb;
EXEC sp_xml_removedocument @h;
二、資料表 → XML 匯出
DECLARE @x XML = (SELECT * FROM Xmltable FOR XML AUTO, TYPE);
SELECT @x; -- 輸出 XML 型別結果
三、類型化 XML 欄位操作 (xml 資料類型方法)
CREATE TABLE xmlt(ID INT PRIMARY KEY, xCol XML NOT NULL);
INSERT INTO xmlt VALUES (1, N'1...');
INSERT INTO xmlt VALUES (2, N'11900-1-11');
1. query() - 回傳 XML 片段
SELECT xCol.query('/Xmltables/Xmltable[@Name="1"]') FROM xmlt WHERE ID=1;
SELECT xCol.query('/Xmltables/Xmltable[@Name="1"]/text()') FROM xmlt WHERE ID=1; -- 只取文字
2. value() - 回傳標量值 (需指定 XQuery 單一值)
SELECT xCol.value('data(/Xmltables/Xmltable[@Name="5"]/@NowTime)[1]', 'NVARCHAR(MAX)') FROM xmlt WHERE ID=1;
SELECT xCol.value('data(/Xmltables/Xmltable/Name)[1]', 'NVARCHAR(MAX)') FROM xmlt WHERE ID=2;
3. nodes() - 將 XML 節點拆為關聯式資料列 (配合 CROSS APPLY)
SELECT nref.value('@Name', 'VARCHAR(MAX)') AS Name,
nref.value('@NowTime', 'VARCHAR(MAX)') AS NowTime
FROM xmlt CROSS APPLY xCol.nodes('/Xmltables/Xmltable') AS R(nref)
WHERE ID=1;
4. exist() - 判斷節點是否存在 (可用於 WHERE 過濾)
SELECT * FROM docs WHERE xCol.exist('/root/dbo.Xmltable') = 1;
5. modify() - 更新 XML 內容 (INSERT/DELETE/REPLACE)
UPDATE docs SET xCol.modify('
insert
after (/doc/section[@num=1])[1]');
四、從檔案直接查詢 XML (無需載入表)
SELECT CAST(T.c AS XML).query('/root/dbo.Xmltable/name')
FROM OPENROWSET(BULK 'E:\xml.xml', SINGLE_BLOB) T(c);
五、XML 索引效能優化
- 建立主 XML 索引：
CREATE PRIMARY XML INDEX idx_xml_xCol ON xmlt(xCol);
- 建立次要索引 (PATH/VALUE/PROPERTY) 針對特定查詢模式優化。
六、實戰提醒
OPENXML 需配對 sp_xml_preparedocument/removedocument，記憶體佔用大，大量資料建議改用 xml 型別欄位 + nodes()。FOR XML AUTO, TYPE 直接產生 xml 型別，避免字串截斷。value() 的 XQuery 必須保證回傳單一值，否則報錯；用 [1] 限定第一個。- 命名空間處理：
WITH XMLNAMESPACES(DEFAULT 'uri') 或在 XQuery 中宣告。
