sql - 事务 SQL XQuery XML 查询

Question

DECLARE @tProduct TABLE (
  [pProductId] [smallint] IDENTITY(1,1) PRIMARY KEY NOT NULl,
  [ProductDetails] [xml] NOT NULL
)

  INSERT @tProduct 
    ( [ProductDetails] ) 
  VALUES 
    ( N'<product>
         <placeholder name="ProductHeader"><control name="pdRequiredMainHeading"><text position="placeholder1">Blah blah</text></control></placeholder>
         <placeholder name="LeftColumn">
           <control name="pdRequiredSubHeading"><text position="placeholder1">Blah blah</text><text position="placeholder2">Blah blah</text></control>
           <control name="pdRequiredParagraph"><text position="placeholder1">Blah blah</text></control>
           <control name="pdOverlinedUnderlinedHeading"><text position="placeholder1">Blah blah</text></control>
           <control name="pdParagraph"><text position="placeholder1">Blah blah</text></control>
          </placeholder>
          <placeholder name="RightColumn">
              <control name="pdRequiredMediumImage"><image position="placeholder1" alt="Blah blah">Blahblah.gif</image></control>
              <control name="pdMediumImage"><image position="placeholder1" alt="">BlahBlah2.gif</image></control>
              <control name="pdRoundedBorderHeadingUnorderedList"><text position="placeholder1">Blah blah</text><ul position="placeholder2"><li>Blah blah</li></ul></control>
              <control name="pdMediumImage"><image position="placeholder1" alt="">The-Image-I-Want-1.gif</image></control>
          </placeholder>
       </product>' )

  INSERT @tProduct 
    ( [ProductDetails] ) 
  VALUES 
    ( N'<product>
         <placeholder name="ProductHeader"><control name="pdRequiredMainHeading"><text position="placeholder1">Blah blah</text></control></placeholder>
         <placeholder name="LeftColumn">
           <control name="pdRequiredSubHeading"><text position="placeholder1">Blah blah</text><text position="placeholder2">Blah blah</text></control>
           <control name="pdRequiredParagraph"><text position="placeholder1">Blah blah</text></control>
           <control name="pdOverlinedUnderlinedHeading"><text position="placeholder1">Blah blah</text></control>
           <control name="pdParagraph"><text position="placeholder1">Blah blah</text></control>
         </placeholder>
         <placeholder name="RightColumn">
           <control name="pdRequiredMediumImage"><image position="placeholder1" alt="Blah blah">Blahblah.gif</image></control>
           <control name="pdRoundedBorderHeading"><text position="placeholder1">Blah blah</text><ul position="placeholder2"><li>Blah blah</li></ul></control>
           <control name="pdMediumImage"><image position="placeholder1" alt="">The-Image-I-Want-12.gif</image></control>
         </placeholder>
       </product>' )

  INSERT @tProduct 
    ( [ProductDetails] ) 
  VALUES 
    ( N'<product>
         <placeholder name="ProductHeader"><control name="pdRequiredMainHeading"><text position="placeholder1">Blah blah</text></control></placeholder>
         <placeholder name="LeftColumn">
           <control name="pdRequiredSubHeading"><text position="placeholder1">Blah blah</text><text position="placeholder2">Blah blah</text></control>
           <control name="pdRequiredParagraph"><text position="placeholder1">Blah blah</text></control>
           <control name="pdOverlinedUnderlinedHeading"><text position="placeholder1">Blah blah</text></control>
           <control name="pdParagraph"><text position="placeholder1">Blah blah</text></control>
         </placeholder>
         <placeholder name="RightColumn">
           <control name="pdRequiredMediumImage"><image position="placeholder1" alt="">The-Image-I-Want-1.gif123.gif</image></control>
           <control name="pdRoundedBorderHeadingUnorderedList"><text position="placeholder1">Blah blah</text><ul position="placeholder2"><li>Blah blah</li></ul></control>
           <control name="pdMediumImage"><image position="placeholder1" alt="Blah blah">Blahblah.gif</image></control>
         </placeholder>
       </product>' )

  INSERT @tProduct 
    ( [ProductDetails] ) 
  VALUES 
    ( N'<product>
         <placeholder name="ProductHeader"><control name="pdRequiredMainHeading"><text position="placeholder1">Blah blah</text></control></placeholder>
         <placeholder name="LeftColumn">
           <control name="pdRequiredSubHeading"><text position="placeholder1">Blah blah</text><text position="placeholder2">Blah blah</text></control>
           <control name="pdRequiredParagraph"><text position="placeholder1">Blah blah</text></control>
           <control name="pdOverlinedUnderlinedHeading"><text position="placeholder1">Blah blah</text></control>
           <control name="pdParagraph"><text position="placeholder1">Blah blah</text></control>
         </placeholder>
         <placeholder name="RightColumn">
           <control name="pdRequiredMediumImage"><image position="placeholder1" alt="">The-Image-I-Want-1.gif1234.gif</image></control>
           <control name="pdRoundedBorder"><text position="placeholder1">Blah blah</text><ul position="placeholder2"><li>Blah blah</li></ul></control>
           <control name="pdMediumImage"><image position="placeholder1" alt="Blah blah">Blahblah.gif</image></control>
         </placeholder>
       </product>' )

  -- ITS AN UPDATE I WANT BUT EVEN THIS I CANT GET TO WORK AS IT DOESNT BRING BACK EVERY IMAGE

  SELECT pProductId, ProductDetails
  FROM @tProduct
  WHERE (ProductDetails.nodes('(//product/placeholder/control/image)') LIKE 'The-Image-I-Want-%')

我尝试了各种版本，包括 WHERE ProductDetails.value 但我还是可以带回一些节点，但不是全部。

我正在尝试创建一个更新查询，从 Image-I-Want-.gif 中删除数字。

即

 Image-I-Want-1.gif   becomes Image-I-Want-.gif

 Image-I-Want-12.gif  becomes Image-I-Want-.gif

 Image-I-Want-123.gif becomes Image-I-Want-.gif

等等等等

但我什至不能让它选择所有需要的图像，更不用说更新它们了。它的 xQuery 语法我不能完全正确，也找不到一个很好的例子，因为到目前为止我尝试的一切都出错了。

我可以用 C# 或一些等效脚本编写此脚本，但我真的很想知道如何使用 xQuery 在 Transact-Sql 中执行此操作，如果可能的话不使用循环等，例如简单的更新查询。

Answer 1

查询:

select pProductId, ProductDetails 
from @tProduct
where ProductDetails.exist('/product/placeholder/control/image[contains(., "The-Image-I-Want-")]') = 1

更新:

update @tProduct
set ProductDetails.modify('replace value of (/product/placeholder/control/image[contains(., "The-Image-I-Want-")]/text())[1] with "The-Image-I-Want-.gif"')
where ProductDetails.exist('/product/placeholder/control/image[contains(., "The-Image-I-Want-")]') = 1