SQL XML column - update child node value based on other nodes

Question

I am not used to use xml in an sql column and have a question about updating the content of that column.

I have a table (TableXML) with a column (ColumnXML) containing the following xml hierarchy : Xml/Content/Queues/list/Item/

Each Item have a /Name, a list of PluginsProperties/Item

and each of these other item have a /key and value

For example :

<Xml>
    <Content Tr="1">
        <Queues Tr="12">
            <list Tr="13">
                <Item Tr="14">
                    <Name Tr="2">Data Load Exception</Name>
                    <PluginProperties Tr="15">
                        <Item Tr="16">
                            <key Tr="2">MSMQQueueType</key>
                            <value Tr="2">PrivateQueue</value>
                        </Item>
                        ...........more items
                    </PluginProperties>
                </Item>
                ...........more items
            </list>
        </Queues>
    </Content>
</Xml>

What I would like to do :

Update the value of the /Xml/Content/Queues/list/Item/PluginProperties/Item/value tag to PublicQueue

where the /Xml/Content/Queues/list/Item/PluginProperties/Item/key is MSMQQueueType

and the /Xml/Content/Queues/list/Item/Name is Data Load Exception

No "Queue item" other than the one with name Data Load Exception should be affected and no "PluginProperties item" other than the one with key MSMQQueueType should be affected for it.

Thanks! =)

Answer 1

you can use replace value of clause to do this

if it was a single node then it can be done in with single statement like below

     update TableXML
     set columnXML.modify('
            replace value of    ((/Xml/Content/Queues/list/Item/PluginProperties/Item/value)[1]/text())[1] with ''PublicQueue''')
 where columnXML.value('((/Xml/Content/Queues/list/Item/PluginProperties/Item/key)[1]/text())[1]','varchar(50)') = 'MSMQQueueType' 
  and columnXML.value('((/Xml/Content/Queues/list/Item/Name)[1]/text())[1]','varchar(50)') = 'Data Load Exception' 

AS there exists many nodes, we need to get the count of the nodes and do it using while loop like below

declare @elements int

select @elements = ISNULL(columnXML.value('count(/Xml/Content/Queues/list/Item/PluginProperties/Item)', 'int'),0)
from TableXML


while @elements > 0 
begin

  update TableXML
  set columnXML.modify
    ('replace value of ((/Xml/Content/Queues/list/Item/PluginProperties/Item/value)[sql:variable("@elements")]/text())[1]
      with ''PublicQueue''')
  where columnXML.value('((/Xml/Content/Queues/list/Item/PluginProperties/Item/key)[sql:variable("@elements")]/text())[1]','varchar(50)') = 'MSMQQueueType' 
  and columnXML.value('((/Xml/Content/Queues/list/Item/Name)[sql:variable("@elements")]/text())[1]','varchar(50)') = 'Data Load Exception' 


  set @elements = @elements - 1
end