Why do I receive a Mutator error when modifying 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, Procedure UpPro, Line 115
Mutator 'modify()' on '@xml' cannot be called on a null value.

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

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

Just to test out the code, I just tried the following and it 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

Keeps printing NOT WORK

Original column (chtml) data:

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

After the above SP executes, the gender should be F and not M

Bulldozer

ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. See details here And if your data is XML - why aren't you use the XML datatype to store it? It's more efficient than a "simple" varchar or even worse: ntext – Quebec

@Quebec Thanks for the response. It is a CMS which is still using ntext unfortunately, hence why I am casting it at the end. My modify statement should work correctly, because there is a value there and not a null. – Bulldozer

Answer 1

The error actually says it all, @XML is null and that is not allowed.

Repro:

declare @X xml;

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

Msg 5302, Level 16, State 1, Line 4 Mutator 'modify()' on '@X' cannot be called on a null value.

Check for null before you modfy.

declare @X xml;

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

Lucullus

I have more @X.modify() in the stored procedure and this was the first one. Do I have to check each time or just the first time? That worked by the way. Thank you. – Bulldozer

Actually nevermind, it didn't work. I don't get the error anymore but it never executes the .modify() statement. I updated my question. – Bulldozer

When the stored procedure first executes, @xml is always null because there is nothing in it yet. I just realized, you helped me in my last question I had about this. :) – Bulldozer

@SiKni8 I don't get it. Why do you have a parameter if it is always null? – Lucullus

I wanted to use a global variable which I can use inside multiple BEGIN and END statement. If I send you partial code can you show me where the error is? It WAS working, not sure why it isn't now. – Bulldozer

Global variable? No such thing in SQL Server. – Lucullus

Sorry I meant use a variable that can be used through out the stored procedure. – Bulldozer

@SiKni8 you need to assign the value in the column to the variable before you modify. – Lucullus

Let us continue this discussion in chat. – Bulldozer

No need to use a parameter for that. Local variable is there in the entire procedure. – Lucullus

I invited you to a chat. I did assign @xml at the beginning of my SP – Bulldozer

#32977544 – Bulldozer