[SOLVED] Updating SQL Server XML node

Updating SQL Server XML node

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

Solution

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