如何在SQL脚本中使用新值更新XML元素

Question

我在XYZ表的一列中有XML
，并且我需要Amount使用新值而不是0.00更新元素，并且使用两个不同值而不是空白的PolicyReference和AccountReference元素来更新。

例如：

<PolicyReference>7657576567</PolicyReference>
<AccountReference>7657576875</AccountReference>

这是我在专栏中的XML：

<document>
    <StatusCode>ACV</StatusCode>
    <PaymentMethodDetail>
        <EFT>
            <AccountNumber>123456789</AccountNumber>
            <AccountName>ABCDEFGHIJK</AccountName>
        </EFT>
    </PaymentMethodDetail>
    <PaymentExtendedData>
        <CHECK>
            <Source>System</Source>
            <SourceType>ACH</SourceType>
        </CHECK>
    </PaymentExtendedData>
    <PostMarkDate />
    <EntryUserId>1</EntryUserId>
    <Amount>0.00</Amount>
    <EntryDate />
    <PolicyLineOfBusiness>LOL</PolicyLineOfBusiness>
    <PolicyReference />
    <AccountReference />
    <AccountId>2034001793</AccountId>
</document>

这是我尝试过的：

UPDATE XYZ
SET XmlPayload.modify('replace value of (//document/PolicyReference/)[1] with "<PolicyReference>275654</PolicyReference>"')
WHERE PaymentSearchId = 18785

我收到一个错误：

消息9341，级别16，状态1，第4行
XQuery [XYZ.XmlPayload.modify（）]：“）”附近的语法错误，应包含步骤表达式

Answer 1

我认为这是一个很好的问题，因为它提出了一个有趣的挑战，即拥有一个没有文本值的现有元素。这与简单地添加新元素或替换现有元素的内容不同。

但是，首先，您提供的XML已损坏。如果那是您要接收的XML，则还有其他问题。例如，在您最初的问题中，您</AccountReference>本身就有一个无效的语法。我<AccountReference/>在您的问题和示例中都对此进行了纠正。

对于空的XML元素，您需要调用insert textXML.modify方法的DML。

DECLARE @xml XML = 
'<document>
    <StatusCode>ACV</StatusCode>
    <PaymentMethodDetail>
       <EFT>
          <AccountNumber>123456789</AccountNumber>
          <AccountName>ABCDEFGHIJK</AccountName>
       </EFT>
    </PaymentMethodDetail>
    <PaymentExtendedData>
       <CHECK>
          <Source>System</Source>
          <SourceType>ACH</SourceType>
        </CHECK>
    </PaymentExtendedData>
    <PostMarkDate />
    <EntryUserId>1</EntryUserId>
    <Amount>0.00</Amount>
    <EntryDate />
    <PolicyLineOfBusiness>LOL</PolicyLineOfBusiness>
    <PolicyReference />
    <AccountReference />
    <AccountId>2034001793</AccountId>
</document>';

DECLARE 
    @Amount DECIMAL(18,2) = 99.95,
    @AccountReference VARCHAR(20) = '7657576875',
    @PolicyReference VARCHAR(20) = '7657576567';

/* Update Amount */
SET @xml.modify('
    replace value of (/document/Amount/text())[1]
    with sql:variable("@Amount")
');

/* Insert the AccountReference text */
SET @xml.modify('
    insert text {sql:variable("@AccountReference")} into (/document/AccountReference[1])[1]
');

/* Insert the PolicyReference text */
SET @xml.modify('
    insert text {sql:variable("@PolicyReference")} into (/document/PolicyReference[1])[1]
');

/* Show updated XML */
SELECT @xml;

更新后的XML现在是：

<document>
  <StatusCode>ACV</StatusCode>
  <PaymentMethodDetail>
    <EFT>
      <AccountNumber>123456789</AccountNumber>
      <AccountName>ABCDEFGHIJK</AccountName>
    </EFT>
  </PaymentMethodDetail>
  <PaymentExtendedData>
    <CHECK>
      <Source>System</Source>
      <SourceType>ACH</SourceType>
    </CHECK>
  </PaymentExtendedData>
  <PostMarkDate />
  <EntryUserId>1</EntryUserId>
  <Amount>99.95</Amount>
  <EntryDate />
  <PolicyLineOfBusiness>LOL</PolicyLineOfBusiness>
  <PolicyReference>7657576567</PolicyReference>
  <AccountReference>7657576875</AccountReference>
  <AccountId>2034001793</AccountId>
</document>

更新表的示例：

DECLARE @xyz TABLE ( PaymentSearchId INT, XmlPayload XML );
INSERT INTO @xyz VALUES ( 18785, 
'<document>
    <StatusCode>ACV</StatusCode>
    <PaymentMethodDetail>
       <EFT>
          <AccountNumber>123456789</AccountNumber>
          <AccountName>ABCDEFGHIJK</AccountName>
       </EFT>
    </PaymentMethodDetail>
    <PaymentExtendedData>
       <CHECK>
          <Source>System</Source>
          <SourceType>ACH</SourceType>
        </CHECK>
    </PaymentExtendedData>
    <PostMarkDate />
    <EntryUserId>1</EntryUserId>
    <Amount>0.00</Amount>
    <EntryDate />
    <PolicyLineOfBusiness>LOL</PolicyLineOfBusiness>
    <PolicyReference />
    <AccountReference />
    <AccountId>2034001793</AccountId>
</document>' );

DECLARE 
    @PaymentSearchId INT = 18785,
    @Amount DECIMAL(18,2) = 99.95,
    @AccountReference VARCHAR(20) = '7657576875',
    @PolicyReference VARCHAR(20) = '7657576567';

/* Update Amount */
UPDATE @xyz
SET XmlPayload.modify('
    replace value of (/document/Amount/text())[1]
    with sql:variable("@Amount")
')
WHERE PaymentSearchId = @PaymentSearchId;

/* Insert the AccountReference text */
UPDATE @xyz
SET XmlPayload.modify('
    insert text {sql:variable("@AccountReference")} into (/document/AccountReference[1])[1]
')
WHERE PaymentSearchId = @PaymentSearchId;

/* Insert the PolicyReference text */
UPDATE @xyz
SET XmlPayload.modify('
    insert text {sql:variable("@PolicyReference")} into (/document/PolicyReference[1])[1]
')
WHERE PaymentSearchId = @PaymentSearchId;

/* Show updated XML */
SELECT XmlPayload FROM @xyz WHERE PaymentSearchId = @PaymentSearchId;