SQL 跨資料庫聯合查詢與 XML 解析實戰：多表 JOIN + XQuery 綜合範例

場景描述

某系統需整合：主單據表 Ex_Bill、系統配置表 Ex_System_Cfg、遠端工作流引擎表 [10.2.3.39].AspireworkFlow.dbo.RepeaingTable、擴展表 EX_BillExtension，並解析 XML 欄位提取結構化資料，最後以 UNION 合併兩種業務狀態結果。

核心技術點

1. 跨伺服器查詢： 四段式名稱 [LinkedServer].[Database].[Schema].[Table]。
2. 多次 JOIN 同一遠端表： 用不同別名 c、d、g、h 依 Keyword 過濾不同業務資料。
3. XML 欄位 XQuery 提取： CAST(XmlData AS XML).value('(/List/item/No/text())[1]', 'NVARCHAR(300)')。
4. 字串清洗： REPLACE(REPLACE(SUMMARY, CHAR(10), ''), CHAR(13), '') 移除換行符。
5. UNION 合併： 兩組查詢欄位數/類型/順序完全一致，業務邏輯互斥（SKRXM IS NULL vs IS NOT NULL）。

完整查詢（格式化版）

-- 第一組：SKRXM 為 NULL
SELECT
    a.EBILLNO, a.EMPNAME, a.APPLYDATE, b.HS_NAME,
    REPLACE(REPLACE(a.SUMMARY, CHAR(10), ''), CHAR(13), '') AS SUMMARY,
    CAST(c.XmlData AS XML).value('(/List/item/No/text())[1]', 'NVARCHAR(300)') AS No,
    CAST(c.XmlData AS XML).value('(/List/item/zje/text())[1]', 'NVARCHAR(300)') AS zje,
    CAST(c.XmlData AS XML).value('(/List/item/yfje/text())[1]', 'NVARCHAR(300)') AS yfje,
    CAST(c.XmlData AS XML).value('(/List/item/bcje/text())[1]', 'NVARCHAR(300)') AS bcje,
    CAST(c.XmlData AS XML).value('(/List/item/URL/text())[1]', 'NVARCHAR(300)') AS URL,
    CAST(c.XmlData AS XML).value('(/List/item/Remark/text())[1]', 'NVARCHAR(300)') AS BZ,
    CAST(p.XmlData AS XML).value('(/NewDataSet/Table1/UserName/text())[1]', 'NVARCHAR(500)') AS SKRXM,
    ('https://...?sid=3&mid=7281&PID=' + a.PID) AS bxdljdz
FROM Ex_Bill AS a
LEFT JOIN Ex_System_Cfg AS b
    ON a.BILLSYSTEMID = b.HS_ID AND a.DATASYSTEMID = b.SYSTEM_NAME
LEFT JOIN (SELECT * FROM [10.2.3.39].AspireworkFlow.dbo.RepeaingTable) AS c
    ON c.Keyword = 'URL' AND c.ProcessID = a.PID
LEFT JOIN (SELECT * FROM [10.2.3.39].AspireworkFlow.dbo.RepeaingTable) AS d
    ON (d.Keyword = 'FKXX_New' OR d.Keyword = 'FKXX') AND d.ProcessID = a.PID
LEFT JOIN EX_BillExtension AS p
    ON a.BILLNO = p.BILL_NO
WHERE a.applyempid = 'zhongxun'
  AND a.EBILLNO IS NOT NULL
  AND a.status > 5
  AND a.status NOT IN (200, 100, 7000)
  AND a.APPLYDATE > '2011-01-01'
  AND a.HT = '是'
  AND CAST(d.XmlData AS XML).value('(/List/item/SKRXM/text())[1]', 'NVARCHAR(300)') IS NULL

UNION

-- 第二組：SKRXM 不為 NULL
SELECT
    e.EBILLNO, e.EMPNAME, e.APPLYDATE, f.HS_NAME,
    REPLACE(REPLACE(e.SUMMARY, CHAR(10), ''), CHAR(13), '') AS SUMMARY,
    CAST(g.XmlData AS XML).value('(/List/item/No/text())[1]', 'NVARCHAR(300)') AS No,
    CAST(g.XmlData AS XML).value('(/List/item/zje/text())[1]', 'NVARCHAR(300)') AS zje,
    CAST(g.XmlData AS XML).value('(/List/item/yfje/text())[1]', 'NVARCHAR(300)') AS yfje,
    CAST(g.XmlData AS XML).value('(/List/item/bcje/text())[1]', 'NVARCHAR(300)') AS bcje,
    CAST(g.XmlData AS XML).value('(/List/item/URL/text())[1]', 'NVARCHAR(300)') AS URL,
    CAST(g.XmlData AS XML).value('(/List/item/Remark/text())[1]', 'NVARCHAR(300)') AS BZ,
    CAST(h.XmlData AS XML).value('(/List/item/SKRXM/text())[1]', 'NVARCHAR(300)') AS SKRXM,
    ('https://...?sid=3&mid=7281&PID=' + e.PID) AS bxdljdz
FROM Ex_Bill AS e
LEFT JOIN Ex_System_Cfg AS f
    ON e.BILLSYSTEMID = f.HS_ID AND e.DATASYSTEMID = f.SYSTEM_NAME
LEFT JOIN (SELECT * FROM [10.2.3.39].AspireworkFlow.dbo.RepeaingTable) AS g
    ON g.Keyword = 'URL' AND g.ProcessID = e.PID
LEFT JOIN (SELECT * FROM [10.2.3.39].AspireworkFlow.dbo.RepeaingTable) AS h
    ON (h.Keyword = 'FKXX_New' OR h.Keyword = 'FKXX') AND h.ProcessID = e.PID
WHERE e.applyempid = 'zhongxun'
  AND e.EBILLNO IS NOT NULL
  AND e.status > 5
  AND e.status NOT IN (200, 100, 7000)
  AND e.APPLYDATE > '2011-01-01'
  AND e.HT = '是'
  AND CAST(h.XmlData AS XML).value('(/List/item/SKRXM/text())[1]', 'NVARCHAR(300)') IS NOT NULL

優化建議

• 遠端表重複引用 4 次 → 建立本地暫存表/檢視表預先篩選 Keyword IN ('URL','FKXX','FKXX_New') 再 JOIN。
• XML 解析重複 CAST → 使用 CROSS APPLY 配合 .nodes() 解析一次複用多值。
• UNION → 若確定無重複行用 UNION ALL 避免排序去重開銷。
• 硬編碼 URL/參數 → 移至配置表/函數。

總結

此查詢展示了：跨庫 JOIN、多別名複用遠端表、XQuery XML 解析、字串清洗、UNION 業務合併。邏輯清晰後，SQL 寫作即成水到渠成。