Filter XML node based on multiple xml attributes for update

Question

In the query below I’m updating a value in an xml node in the Items column in table X in the database. Right now I’m filtering based only on the Name attribute. Because there are multiple nodes with the same Name I need to be able to filter on more attributes during the update.

UPDATE dbo.Declarations
    SET Items.modify('replace value of 
 (/Items/Item[@Name=(sql:variable("@ItemName"))]/text())[1] 
 with sql:variable("@Value")')
   WHERE DeclarationId = @DeclarationId
END

Is it possible to select an XML node based on multiple attribute values and then update the value of this node?

Mikael Eriksson · Accepted Answer · 2013-10-01 16:32:48Z

3

You can use and in the predicate and add more checks.

declare @X xml = '
<Items>
  <Item Name = "Name1" Type = "Type1">Value1</Item>
  <Item Name = "Name2" Type = "Type2">Value2</Item>
</Items>
'

declare @Value varchar(10) = 'NewValue2'
declare @Name varchar(10) = 'Name2'
declare @Type varchar(10) = 'Type2'

set @X.modify('
  replace value of (/Items/Item[
                               @Name = sql:variable("@Name") and 
                               @Type = sql:variable("@Type")
                               ]/text())[1]
  with sql:variable("@Value")
')

answered Oct 1, 2013 at 16:32

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Comments

Stuart Ainsworth · Accepted Answer · 2013-10-01 16:29:44Z

0

UPDATE dbo.Declarations
    SET Items.modify('replace value of 
 (/Items/Item[@Name=(sql:variable("@ItemName"))][@Attr=(sql:variable("@newParam"))]/text())[1] 
 with sql:variable("@Value")')
   WHERE DeclarationId = @DeclarationId
END

Should do it; assuming that you have an attribute named Attr and a new sql variable of @newParam.

answered Oct 1, 2013 at 16:29

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Collectives™ on Stack Overflow

Filter XML node based on multiple xml attributes for update

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related