时间戳

首页 > 解决方案 > 在 SQL Server 中更新 XML 数据

sql - 在 SQL Server 中更新 XML 数据

问题描述

我需要将abc_NumberOfHoldDays值更新为空，如下所示。我无法运行查询 - 有人可以帮我检查查询以更新 XML 数据吗？

XML：

<Plans>
<Plan>
  <Coverages>
        <Coverage>
          <Answers>
            <AnswersList>
              <Entry key="abc_NumberOfHoldDays" type="System.String">
                <value>11</value>
              </Entry>
 </Coverages>
  </Coverage>
 </Answers>
 </AnswersList>
</plan>
<Plan>
  <Coverages>
        <Coverage>
          <Answers>
            <AnswersList>
              <Entry key="abc_NumberOfHoldDays" type="System.String">
                <value>12</value>
              </Entry>
 </Coverages>
  </Coverage>
 </Answers>
 </AnswersList>
</plan>

我的查询：

DECLARE @COUNT INT
DECLARE @xml xml

SET @COUNT = (SELECT Data.value ('count((/*/Plans/Plan/Coverages/Coverage/Answers/AnswersList/Entry[@key="abc_NumberOfHoldDays"]/value))', 'INT') FROM  table1)

SET @xml = (SELECT Data FROM table1)

--SELECT @COUNT
--SELECT @xml

WHILE @COUNT > 0
BEGIN
    SET @xml.modify('replace value of (//Plans/Plan/Coverages/Coverage/Answers/AnswersList/Entry[@key="abc_NumberOfHoldDays"][sql:variable("@COUNT")]/text())[1] with 
    (
       (/Plans/Plan/Coverages/Coverage/Answers/AnswersList/Entry[@key="abc_NumberOfHoldDays"][sql:variable("@COUNT")])[1] =''   
    )
')
    SET @COUNT = @COUNT - 1;
END

标签： sqlsql-serverxmlxpath

解决方案

因为需要使用双引号。

declare @xml xml='
<Plans>
<Plan>
  <Coverages>
        <Coverage>
          <Answers>
            <AnswersList>
              <Entry key="abc_NumberOfHoldDays" type="System.String">
                <value>11</value>
              </Entry>
 </AnswersList>
 </Answers>
  </Coverage>
 </Coverages>
</Plan>
<Plan>
  <Coverages>
        <Coverage>
          <Answers>
            <AnswersList>
              <Entry key="abc_NumberOfHoldDays" type="System.String">
                <value>12</value>
              </Entry>
 </AnswersList>
 </Answers>
  </Coverage>
 </Coverages>
</Plan>
</Plans>';

SET @xml.modify('
replace value of (//Entry[@key="abc_NumberOfHoldDays"]/value[1]/text())[1] with ""
');
select @xml;

或者可以完全删除“价值”元素。

SET @xml.modify('delete //Entry[@key="abc_NumberOfHoldDays"]/value');
select @xml;

推荐阅读