Updating SQL Server XML node

Question

I am trying to update a node Qty of my XML which is stored inside a SQL Server XML column, no error occurred but my field did not got updated.

Please advice. Thanks.

XML data copied from SQL Server xml column:

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

Stored procedure:

USE [c1]
GO
/****** Object:  StoredProcedure [dbo].[Campaign_InsertRewardsVoucher]    Script Date: 10/22/2011 23:33:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
*/
ALTER PROCEDURE [dbo].[Campaign_InsertRewardsVoucher]  

    @voucherXML         XML, -- @voucherXML is the XML pass from my SQL server 

AS  
BEGIN

declare @changedVal int
set @changedVal = 4 

UPDATE  [Customers] 
SET  voucherXML.modify('replace value of (/CampaignVoucher/Qty/text())[1] with "0"') 
WHERE  voucherXML.value('(/CampaignVoucher/Qty)[1]', 'int') = @changedVal 


END

marc_s · Accepted Answer · 2011-10-22 17:53:06Z

2

You're ignoring the root level node <ArrayOfCampaignVoucher> - try this:

UPDATE 
   dbo.[Customers] 
SET 
   voucherXML.modify('replace value of (/ArrayOfCampaignVoucher/CampaignVoucher/Qty/text())[1] with "0"') 
WHERE  
   voucherXML.value('(/ArrayOfCampaignVoucher/CampaignVoucher/Qty)[1]', 'int') = @changedVal

answered Oct 22, 2011 at 17:53

marc_s

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

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

Comments

Anatoly · Accepted Answer · 2020-11-25 06:50:36Z

0

Or, you could do the following, assuming SQL Server supports the descendent-or-self axis, and assuming that Qty has no other ancestors:

UPDATE 
  dbo.[Customers] 
SET 
  voucherXML.modify('replace value of (//Qty/text())[1] with "0"') 
WHERE  
  voucherXML.value('(//Qty)[1]', 'int') = @changedVal

But it seems like you have multiple CampaignVoucher nodes per Array, and that you wouldn't want to wholesale replace the first instance of a value with a new value. Instead, wouldn't you want to restrict the update to a voucher with a specific VouCode?

edited Nov 25, 2020 at 6:50

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

answered Oct 23, 2011 at 16:13

kstirman

2212 silver badges3 bronze badges

Collectives™ on Stack Overflow

Updating SQL Server XML node

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related