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

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 — marc_s
– marc_s, Commented Oct 6, 2015 at 16:14
@marc_s 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. — Si8
– Si8, Commented Oct 6, 2015 at 16:14

Mikael Eriksson · Accepted Answer · 2015-10-06 16:30:17Z

2

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"');

answered Oct 6, 2015 at 16:30

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

12 Comments

Si8 Over a year ago

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.

Si8 Over a year ago

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

Si8 Over a year ago

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. :)

Mikael Eriksson Over a year ago

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

Si8 Over a year ago

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.

|

Collectives™ on Stack Overflow

Why do I receive a Mutator error when modifying an Xml value in Sql

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related