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.

Answer 1

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

Answer 2

This may get you off the hump.

Replace #HolderTable with the name of your table.

SELECT T2.myAlias.query('./../PriceLevel[1]').value('.' , 'varchar(64)') as MyXmlFragmentValue
FROM   #HolderTable
CROSS APPLY OrderXML.nodes('/InternalOrderDetails/InternalOrderDetail/Item_Number') as T2(myAlias) 


SELECT T2.myAlias.query('.') as MyXmlFragment
FROM   #HolderTable
CROSS APPLY OrderXML.nodes('/InternalOrderDetails/InternalOrderDetail/Item_Number') as T2(myAlias) 

EDIT:

UPDATE 
   #HolderTable
SET 
   OrderXML.modify('replace value of (/InternalOrderDetails/InternalOrderDetail/PriceLevel/text())[1] with "MyNewValue"') 
WHERE  
   OrderXML.value('(/InternalOrderDetails/InternalOrderDetail/PriceLevel)[1]', 'varchar(64)') = 'FREE' 

print @@ROWCOUNT

Your issue is the [1] in the above. Why did I put it there?

Here is a sentence from the URL listed below.

Note that the target being updated must be, at most, one node that is explicitly specified in the path expression by adding a "[1]" at the end of the expression.

http://msdn.microsoft.com/en-us/library/ms190675.aspx

EDIT.

I think I've discovered the the root of your frustration. (No fix, just the problem).

Note below, the second query works.

So I think the [1] is some cases is saying "only ~~search~~ the first node".....and not (as you and I were hoping)...... "use the first node..after you find a match".

UPDATE 
   #HolderTable
SET 
   OrderXML.modify('replace value of (/InternalOrderDetails/InternalOrderDetail/PriceLevel/text())[1] with "MyNewValue001"') 
WHERE  
   OrderXML.value('(/InternalOrderDetails/InternalOrderDetail/PriceLevel[text() = "NONFREE"])[1]', 'varchar(64)') = 'NONFREE' 
    /* and OrderXML.value('(/InternalOrderDetails/InternalOrderDetail/Item_Number)[1]', 'varchar(64)') like '%COTRGUID' */



UPDATE 
   #HolderTable
SET 
   OrderXML.modify('replace value of (/InternalOrderDetails/InternalOrderDetail/PriceLevel/text())[1] with "MyNewValue002"') 
WHERE  
   OrderXML.value('(/InternalOrderDetails/InternalOrderDetail/PriceLevel[text() = "FREE"])[1]', 'varchar(64)') = 'FREE' 

Answer 3

Try this :

;with InternalOrderDetail as (SELECT  id,
       Tbl.Col.value('Item_Number[1]', 'varchar(40)') Item_Number,  
       Tbl.Col.value('CountOfNumber[1]', 'int') CountOfNumber,  
case 
when Tbl.Col.value('Item_Number[1]', 'varchar(40)') like '%COTRGUID'
      and Tbl.Col.value('PriceLevel[1]', 'varchar(40)')='NONFREE'
                   then 'FREE'
else
       Tbl.Col.value('PriceLevel[1]', 'varchar(40)')
end
PriceLevel

FROM   (select id ,orderxml from demo) 
as a cross apply orderxml.nodes('//InternalOrderDetail')
as  
tbl(col) ) ,
cte_data as(SELECT 
  ID,
'<InternalOrderDetails>'+(SELECT ITEM_NUMBER,COUNTOFNUMBER,PRICELEVEL
FROM InternalOrderDetail
where ID=Results.ID
FOR XML AUTO, ELEMENTS)+'</InternalOrderDetails>' as XML_data
FROM InternalOrderDetail Results
GROUP BY ID)
update demo set orderxml=cast(xml_data as xml)
from demo 
inner join cte_data on demo.id=cte_data.id
where cast(orderxml as varchar(2000))!=xml_data;

select * from demo;

SQL Fiddle

I have handled following cases :
1. As required both where clause in question.
2. It will update all <Item_Number> like '%COTRGUID' and <PriceLevel>= NONFREE in one node, not just the first one.

It may require minor changes for your data and tables.