如何将SQL表中XML类型字段的数据当作普通SQL记录查询？

嘿，我刚好熟悉SQL里XML字段的操作，你之前踩的坑我也遇到过！咱们先把查询的问题解决，再一步步覆盖插入、删除的场景，让你能像操作普通表一样搞定这个XML字段。

你之前的语句有两个小问题：

1. nodes()方法的路径错了——应该定位到每个<ClaimExpense>节点，而不是直接到<GLAccount>，不然没法同时获取其他字段；
2. SQL Server的XML节点索引是从1开始的，不是0，所以GLAccount[0]是找不到值的。

假设你的表叫ClaimRecords，XML字段叫ExpenseXML，正确的查询语句应该是这样的：

SELECT
    -- 提取每个ClaimExpense节点下的字段
    expense.value('(ClaimNo)[1]', 'varchar(50)') AS ClaimNo,
    expense.value('(Office)[1]', 'varchar(10)') AS Office,
    expense.value('(GLAccount)[1]', 'varchar(20)') AS GLAccount,
    expense.value('(LCAmount)[1]', 'decimal(18,2)') AS LCAmount,
    expense.value('(VatAmount)[1]', 'decimal(18,2)') AS VatAmount,
    expense.value('(EmpCode)[1]', 'varchar(20)') AS EmpCode
FROM ClaimRecords
-- 把XML拆成每行对应一个ClaimExpense节点
CROSS APPLY ExpenseXML.nodes('/NewDataSet/ClaimExpense') AS Expenses(expense)

这样就能把XML里的每个报销项转换成普通的SQL行，你还可以加WHERE条件筛选，比如WHERE expense.value('(ClaimItemNo)[1]', 'varchar(20)') = 'ITM-004'。

如果想把XML数据临时转成普通表方便操作，还可以用SELECT ... INTO生成临时表：

SELECT
    expense.value('(ClaimNo)[1]', 'varchar(50)') AS ClaimNo,
    expense.value('(Office)[1]', 'varchar(10)') AS Office,
    -- 把所有需要的字段都列出来
    expense.value('(EmpCode)[1]', 'varchar(20)') AS EmpCode
INTO #TempExpenses
FROM ClaimRecords
CROSS APPLY ExpenseXML.nodes('/NewDataSet/ClaimExpense') AS Expenses(expense)

-- 之后就像操作普通表一样用它
SELECT * FROM #TempExpenses WHERE GLAccount = '10000000'

更新某个节点的值 #

比如要把ClaimItemNo为ITM-004的GLAccount改成20000000：

UPDATE ClaimRecords
SET ExpenseXML.modify('
    replace value of (/NewDataSet/ClaimExpense[ClaimItemNo="ITM-004"]/GLAccount/text())[1]
    with "20000000"
')
-- 加筛选条件定位到目标记录
WHERE ExpenseXML.value('(/NewDataSet/ClaimExpense/ClaimNo)[1]', 'varchar(50)') = '3003-LOB-0003'

删除指定的ClaimExpense节点 #

比如要删掉ClaimItemNo为ITM-005的报销项：

UPDATE ClaimRecords
SET ExpenseXML.modify('
    delete /NewDataSet/ClaimExpense[ClaimItemNo="ITM-005"]
')
WHERE ExpenseXML.value('(/NewDataSet/ClaimExpense/ClaimNo)[1]', 'varchar(50)') = '3003-LOB-0003'

先定义要插入的XML片段，再用insert语法添加到<NewDataSet>节点下：

-- 定义新的报销项XML
DECLARE @newExpense XML = '
<ClaimExpense>
    <ClaimNo>3003-LOB-0003</ClaimNo>
    <Office>3003</Office>
    <BranchId>1</BranchId>
    <CostCenterId>35</CostCenterId>
    <ServiceLineId>14</ServiceLineId>
    <ProjectId>62</ProjectId>
    <LCAmountCurr>AED</LCAmountCurr>
    <LCAmount>150.00</LCAmount>
    <FCCurr>AED</FCCurr>
    <FCAmount>150</FCAmount>
    <ExchangeRate>1</ExchangeRate>
    <ExpenseDate>2020-11-04T00:00:00+04:00</ExpenseDate>
    <ClaimItemNo>ITM-006</ClaimItemNo>
    <GLAccount>10000000</GLAccount>
    <ClaimType>LOB</ClaimType>
    <ForPayment>150.00</ForPayment>
    <ForDeduction>0.00</ForDeduction>
    <EmpCode>2019-1194</EmpCode>
</ClaimExpense>'

-- 插入到目标XML中
UPDATE ClaimRecords
SET ExpenseXML.modify('
    insert sql:variable("@newExpense") as last into (/NewDataSet)[1]
')
WHERE ExpenseXML.value('(/NewDataSet/ClaimExpense/ClaimNo)[1]', 'varchar(50)') = '3003-LOB-0003'

小提示 #

• 上面的语法都是针对SQL Server的，如果是其他数据库（比如PostgreSQL、MySQL），XML操作的函数会不一样，需要调整；
• 复杂的XML操作可以先转换成临时表处理，再把结果转回XML更新回原表，这样更直观。

内容的提问来源于stack exchange，提问作者Stacky