Updating XML attribute in SQL Server XML column
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]')
k80sg
Updated on June 14, 2022Comments
-
k80sg almost 2 years
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>
-
k80sg over 12 yearsThanks 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 12 years@user415795: check out this three-part series on "15seconds" : 15seconds.com/issue/050803.htm