Update a SQL XML Field node value for a given attribute

Question

I have a table with the following format:

int IndexField
xml ConfigField

A ConfigField field value might look similar to

 <Parameters>
      <Parameter Type="Config" Name="Prefix">hd</Parameter>
      <Parameter Type="Config" Name="PgNumber">2</Parameter>
      <Parameter Type="Config" Name="IsValid">False</Parameter>
    </Parameters>

I would like to know the SQL Server syntax to modifythe Parameters XML node for the PgNumber value from 2 to 3 where IndexField = 22

Thanks

har07 · Accepted Answer · 2015-07-31 01:28:40Z

1

This is one possible XQuery expression to be passed to the SQL Server's modify() method for updating value of <Parameter Name='PgNumber'> element :

UPDATE MyTable
SET ConfigField.modify('
        replace value of 
            (/Parameters/Parameter[@Name="PgNumber"]/text())[1] 
        with 3
    ')
WHERE IndexField = 22

Sqlfiddle Demo

Notice that in XPath/XQuery you need to put @ at the beginning of attribute name to reference an XML attribute.

answered Jul 31, 2015 at 1:28

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

krivtom · Accepted Answer · 2015-07-31 04:06:16Z

1

You can use function modify() and correct XQuery to set the value that you need. The syntax is quite tricky though. In your case you should be able to use such query:

UPDATE YourTableName
SET ConfigField.modify('replace value of (/Parameters/Pararameter[@Name="PgNumber"]/text())[1] with "3"')
WHERE IndexField = 22

edited Jul 31, 2015 at 4:06

answered Jul 30, 2015 at 18:49

krivtom

25k10 gold badges53 silver badges54 bronze badges

4 Comments

jvoigt Over a year ago

I ran that and the message said 1 row affected but when i select again it does not look as if the PgNumber was changed

jvoigt Over a year ago

not sure if this matters but I should add that I am using sql 2012. thanks so much for any help

krivtom Over a year ago

@jvoigt Sorry, it was my typo - I missed @ symbol, which is required for attribute values. I updated the answer, it should be working now

jvoigt Over a year ago

Yes that was the issue. That worked between your answer and har07 reminder of the @

Collectives™ on Stack Overflow

Update a SQL XML Field node value for a given attribute

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related