CODEWITHSUNDEEP
sql-serverxml
jvoigt
jvoigt

Reputation: 431

Update a SQL XML Field node value for a given attribute

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

Upvotes: 2

Views: 1083

Answers (2)

dotnetom
dotnetom

Reputation: 24901

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

Upvotes: 1

har07
har07

Reputation: 89285

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.

Upvotes: 1

Related Questions