Why am I getting a mutator error when changing an Xml value in Sql

Question

I have the following stored procedure:

ALTER PROCEDURE [dbo].[UpPro] ( @PN varchar(200), @xml xml, @gVa varchar(10), ) AS /* update the gender */ BEGIN SET NOCOUNT ON; Select @gVa = t1.[Gender] From [myDb].[dbo].[myTable1] t1 --replace Value2 and table to the table which is updated through SSIS Where t1.Name = @PN PRINT @gVa //displays 'F' Set @xml.modify('replace value of (/root/Phys/gender/text())[1] with sql:variable("@gVa")'); END /* once all the node has been "temporarily" changed, update the table columns for the provider */ BEGIN --update the table after casting dummy xml variable Update [myDb].[dbo].[TC] Set [chtml] = cast(cast(@xml as nvarchar(max)) as ntext) Where [ctitle] = @PN END 

When I run the query, I get the following error:

Msg 5302 Level 16 State 1 UpPro Procedure Line 115
Mutator 'modify ()' on '@xml' cannot be called with a null value.

How can I resolve the error. I am trying to update the xml value in a column ( chtml ) which is of type ntext in the TC table.

Please let me know if I need to provide more information.

Just to check the code, I just tried the following and still gave the same error:

 DECLARE @gVa varchar(10) DECLARE @xml xml Select @gVa = t1.[Gender] From [myDb].[dbo].[myTable1] t1 --replace Value2 and table to the table which is updated through SSIS Where t1.Name = 'Doctor 1' PRINT @gVa If @xml IS NOT NULL BEGIN Set @xml.modify('replace value of (/root/Phys/gender/text())[1] with sql:variable("@gVa")'); END Else BEGIN PRINT 'NOT WORK' END 

Saves NOT WORK stamp

Source column ( chtml ):

 <?xml version="1.0" encoding="UTF-8"?> <root> <Phys> <gender>M</gender> </Phys> </root> 

After doing the above, the SP gender should be F , not M

Answer 1

The error actually says everything, @XML is null, and this is not valid.

Repro:

 declare @X xml; set @X.modify('replace value of text()[1] with "1"'); 

Msg 5302, Level 16, State 1, Line 4 The mutator 'modify ()' on '@X' cannot call a null value.

Check the null value before you modfy.

 declare @X xml; if @X is not null set @X.modify('replace value of text()[1] with "1"');