Updating XML attribute in SQL Server XML column

Question

I am trying to update a node of in my XML which is stored in a SQL Server XML column, the line below works if my XML is in a XML element but now I somehow need to change it to XML attributes, apparently the line becomes invalid after the change.

Works for XMLElement:

UPDATE  [Customers] 
SET  voucherXML.modify('replace value of (/ArrayOfCampaignVoucher/CampaignVoucher/Qty/text())[1] with "50"') 
WHERE  voucherXML.value('(/ArrayOfCampaignVoucher/CampaignVoucher/VouCode)[1]', 'nvarchar(50)') = @VoucherCode

I tried changing the statement like this but no luck, no errors but QTY values doesn't get change to the value of @NewQuantity:

UPDATE [Customers]  
       SET voucherXML='<ArrayOfCampaignVoucher xmlns:xsd="http://www.w3.org/2001/XMLSchema" Qty="' + CAST(@NewQuantity AS NVARCHAR(16)) + '" />'  
   WHERE voucherXML.value('(/CampaignVoucher/VouCode)[1]', 'nvarchar(50)') = @VoucherCode

This is how my XML looks in the SQL Server XML column:

<ArrayOfCampaignVoucher xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <CampaignVoucher VouCode="Vouc001" Qty="16" />
  <CampaignVoucher VouCode="Vouc002" Qty="18" />
  <CampaignVoucher xsi:nil="true" />
</ArrayOfCampaignVoucher>

removed the asp.net, c# and vb.net tags - has nothing to do with any of those technologies, really.. — marc_s
– marc_s, Commented Oct 23, 2011 at 14:26

marc_s · Accepted Answer · 2011-10-23 17:27:30Z

8

You should use the XQuery functions - not string together your XML like this....

Try this instead:

DECLARE @newquantity INT = 55

UPDATE dbo.Customers
SET voucherXML.modify('replace value of (/ArrayOfCampaignVoucher/CampaignVoucher[@VouCode="Vouc002"]/@Qty)[1] with sql:variable("@NewQuantity") ')

This works for me - the Qty attribute of the node with VouCode="Vouc002" gets updated to the value I defined before in a SQL variable

Update: to insert a new <CampaignVoucher> entry, use XQuery something like this:

UPDATE dbo.Customers
SET voucherXML.modify('insert <CampaignVoucher VouCode="Vouc003" Qty="42" /> 
                       as last into (/ArrayOfCampaignVoucher)[1]')

edited Oct 23, 2011 at 17:27

answered Oct 23, 2011 at 14:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

3 Comments

k80sg Over a year ago

Thanks for the help, would you kindly provide me with an example on inserting a new attribute: adding another row of VouCode and Quantity as well as deleting an attribute based on VouCode. I am trying to source for tutorials on this with little luck. Thanks!

marc_s Over a year ago

@user415795: check out this three-part series on "15seconds" : 15seconds.com/issue/050803.htm

Rory Over a year ago

Here's an answer that shows how to add an attribute: stackoverflow.com/a/46734282/8479

Collectives™ on Stack Overflow

Updating XML attribute in SQL Server XML column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related