sql - 更新在数据库中存储为字符串的 XML 节点
问题描述
我可以了解将 XML 节点的值(每行不同)替换为新值(所有行通用)的 SQL 查询吗?
如果 XML 是:
<?xml version="1.0" encoding="utf-16"?>
<EmployeeDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Name>MyName</Name>
<Age>20</Age>
<Salary>15000</Salary>
</EmployeeDetail>
我需要将所有 XML 列更新<Salary>DifferingSalary</Salary>
为<Salary>10000</Salary>
试过:
DECLARE @salary VARCHAR(10) = '10000';
UPDATE TABLE_NAME
SET [COLUMN_NAME].modify('replace value of
(/EmployeeDetail/Salary/text())[1]
with (sql:variable("@salary"))
');
输出:
无法调用 nvarchar(max) 上的方法。
注意:XML 以字符串形式存储在数据库中。
提前致谢!
解决方案
您可以尝试以下方法。
它显示了在以下情况下如何修改<salary>
元素值:
- xmldata 列是 XML 数据类型。
- xmldata 列是 NVARCHAR(MAX) 数据类型。
SQL #1
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl ( xmldata) VALUES
(N'<?xml version="1.0" encoding="utf-16"?>
<EmployeeDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Name>MyName</Name>
<Age>20</Age>
<Salary>15000</Salary>
</EmployeeDetail>');
-- DDL and sample data population, end
-- before
SELECT * FROM @tbl;
DECLARE @salary VARCHAR(10) = '10000';
UPDATE @tbl
SET xmldata.modify('replace value of
(/EmployeeDetail/Salary/text())[1]
with (sql:variable("@salary"))
');
-- after
SELECT * FROM @tbl;
SQL #2
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata NVARCHAR(MAX));
INSERT INTO @tbl ( xmldata) VALUES
(N'<?xml version="1.0" encoding="utf-16"?>
<EmployeeDetail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Name>MyName</Name>
<Age>20</Age>
<Salary>15000</Salary>
</EmployeeDetail>');
DECLARE @realXML TABLE (ID INT, realXML XML);
-- DDL and sample data population, end
-- populate temporary table
INSERT INTO @realXML
SELECT ID, cast(xmldata AS XML)
FROM @tbl;
DECLARE @salary VARCHAR(10) = '10000';
/*
Unfortunately, we can not assign from a xml.modify() statement.
Modify works on a variable and a column only.
Also, we cannot use modify on a CAST().
*/
-- update temporary table
UPDATE @realXML
SET realXML.modify('replace value of
(/EmployeeDetail/Salary/text())[1]
with (sql:variable("@salary"))
');
-- update real table
UPDATE tbl
SET xmldata = CAST(t.realXML AS NVARCHAR(MAX))
FROM @realXML AS t INNER JOIN @tbl AS tbl
ON tbl.ID = t.ID;
推荐阅读
- reactjs - 如何构建一个 React + Express 项目?
- charts - 同月多列的 Power BI 集群图
- javascript - 如何从不同的按钮调用函数?
- ajax - Ajax 在 IIS7 上加载更多
- javascript - TypeError: undefined is not an object (evalating '_this.props') in my react native project
- c - 如何检查GLUT中是否未按下键?
- java - Java-字符数组接受整个字符串,即使声明的大小较小
- android - 通过 ADB 和端口 5555 控制另一个设备
- python - 在不使用 fstrings 修改精度的情况下从 float 中删除尾随 `.0`
- ruby - 如何检查在另一个方法 rspec 中调用的方法的返回值?