How do I update an XML column in sql server by checking for the value of two nodes including one which needs to do a contains (like) comparison

Question

I have an xml column called OrderXML in an Orders table...
there is an XML XPath like this in the table...

/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail

There InternalOrderDetails contains many InternalOrderDetail nodes like this...

<InternalOrderDetails>
  <InternalOrderDetail>
    <Item_Number>FBL11REFBK</Item_Number>
    <CountOfNumber>10</CountOfNumber>
    <PriceLevel>FREE</PriceLevel>
  </InternalOrderDetail>
  <InternalOrderDetail>
    <Item_Number>FCL13COTRGUID</Item_Number>
    <CountOfNumber>2</CountOfNumber>
    <PriceLevel>NONFREE</PriceLevel>
  </InternalOrderDetail>
</InternalOrderDetails>

My end goal is to modify the XML in the OrderXML column IF the Item_Number of the node contains COTRGUID (like '%COTRGUID') AND the PriceLevel=NONFREE. If that condition is met I want to change the PriceLevel column to equal FREE.

I am having trouble with both creating the xpath expression that finds the correct nodes (using OrderXML.value or OrderXML.exist functions) and updating the XML using the OrderXML.modify function).

I have tried the following for the where clause:

WHERE OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/Item_Number/node())[1]','nvarchar(64)') like '%13COTRGUID'

That does work, but it seems to me that I need to ALSO include my second condition (PriceLevel=NONFREE) in the same where clause and I cannot figure out how to do it. Perhaps I can put in an AND for the second condition like this...

AND OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel/node())[1]','nvarchar(64)') = 'NONFREE'

but I am afraid it will end up operating like an OR since it is an XML query.

Once I get the WHERE clause right I will update the column using a SET like this:

UPDATE Orders SET orderXml.modify('replace value of (/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel[1]/text())[1] with "NONFREE"')

However, I ran this statement on some test data and none of the XML columns where updated (even though it said zz rows effected).

I have been at this for several hours to no avail. Help is appreciated. Thanks.

Solution

if you don't have more than one node with your condition in each row of Orders table, you can use this:

update orders set
    data.modify('
        replace value of 
        (
            /Order/InternalInformation/InternalOrderBreakout/
            InternalOrderHeader/InternalOrderDetails/
            InternalOrderDetail[
                Item_Number[contains(., "COTRGUID")] and
                PriceLevel="NONFREE"
            ]/PriceLevel/text()
        )[1]
        with "FREE"
    ');

sql fiddle demo

If you could have more than one node in one row, there're a several possible solutions, none of each is really elegant, sadly.

• You can reconstruct all xmls in table - sql fiddle demo
• or you can do your updates in the loop - sql fiddle demo