为什么在Sql中修改Xml值时会收到Mutator错误 [英] Why do I receive a Mutator error when modifying an Xml value in Sql

问题描述

我有以下存储过程:

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:

消息5302，第16级，状态1，过程UpPro，第115行
不能使用空值调用'@xml'上的变量'modify()'.

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

如何解决该错误.我正在尝试更新TC表中的ntext类型的列(chtml)中的xml值.

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

继续打印NOT WORK

原始列(chtml)数据:

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

执行上述SP后，gender应该是F而不是M

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

推荐答案

错误实际上说明了一切，@XML为null，这是不允许的.

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

复制:

declare @X xml;

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

消息5302，级别16，状态1，第4行'@X'上的变量'modify()'无法 会被调用为空值.

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

这篇关于为什么在Sql中修改Xml值时会收到Mutator错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！