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.

please check given answers, if there're no good answer for you, you can comment on it to guide people to correct answer, or you can create your own answer and accept it if you have your own solution — Roman Pekar, Sep 25 '13 at 20:09
None of these gave me the perfect solution in that I think what I needed done cannot be done (update multiple nodes that match even when the condition can be true of multiple nodes.) — Seth Spearman, Jun 25 '14 at 15:39

Roman Pekar · Accepted Answer · 2013-08-18T12:12:19.107

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

granadaCoder · Answer 2 · 2013-06-17T21:39:59.890

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'

Ravi Singh · Answer 3 · 2013-06-18T09:29:13.343

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.

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

3 Answers3