Transact SQL XQuery XML Query

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-%')

Ive tried various versions of this including WHERE ProductDetails.value but again I can bring back some nodes but not all.

Im tryin to create an update query that removes the numbers from Image-I-Want-.gif.

i.e.

 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

etc etc.

But I cant even make it select all the required images let alone update them. Its the xQuery syntax I cant quite get right nor can I find a good example as everything I try has errored so far.

I could script this in c# or some equvalent but Id really like to know how to do it in Transact-Sql using xQuery without using a loop if possible etc like a simple update query.

Mikael Eriksson Mikael Eriksson · Accepted Answer · 2011-10-07T05:34:45

The query:

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

The update:

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

Transact SQL XQuery XML Query

1 Answers