Why Do I Receive a Mutator Error When Modifying an Xml Value in SQL

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

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

Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context

The syntax is SET [xml_column].modify, no use of the assignment. Instead of using CASE, fold the variable into the update using the special sql:variable function:

UPDATE #temp 
SET xml_data.modify('insert <type>{sql:variable("@type")}</type> after (/Main/name)[1]');

The mutator 'modify ()' of '@Mydoc' can not be called with a NULL value

You can set @Mydoc like this.

select @Mydoc= (
SELECT Impuesto as 'TotalImpuestoTrasladados',
            (SELECT Importe,TasaCuota AS 'TasaOCuota',TipoFactor,Impuesto
            FROM CDFIDet
            FOR XML RAW('cfdi_trasladado'),type,root('cfdi_traslado'))
    FROM CDFIDet
    FOR XML RAW('cfdi_impuestos'),type )

SQL Update query to update xml in records based on xml node value?

You need to use the modify method on the XML data type.

begin transaction
declare @Cps_Id int;
set @Cps_Id = 236;
declare @Cps_Message nvarchar(1024);
set @Cps_Message = 'updating cpsia message with smoking'; 

select id, CONVERT(xml,[text]) txt into #tmp from SO5954359

select * from #tmp

update #tmp
set txt.modify('replace value of (/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]/CpsiaMessage/text())[1] with sql:variable("@Cps_Message")') 

select * from #tmp

drop table #tmp
rollback 

You could then update the original table by joining the updated temporary table to the original table on the key.

Conditional XML Modify in SQL Server

It is incorrect syntax. You could use 2 separate statements instead:

UPDATE ITS_CSC.[Statement].[StatementSummary]
SET  [XMLData].modify('delete //invoice/account/contactinformation') 
WHERE [XMLData] IS NOT NULL;

UPDATE ITS_CSC.[Statement].[StatementSummary]
SET [SummaryXMLData].modify('delete //invoice/account/contactinformation')
WHERE [SummaryXMLData] IS NOT NULL;

SQL: Delete XML node in an UPDATE statement

You were told already, that it is a very bad idea to store XML in a string type. It is always the best choice to use the appropriate type in your data design.

Besides this, if you have to stick to this design (sometimes we must do quirky things), you might try something along this:

--Creating a mockup-table to simulate your issue:

DECLARE @tbl TABLE(CustomProperties VARCHAR(1000));
INSERT INTO @tbl VALUES
('<CustomProperties
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldA</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldB</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldC</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldD</Key>
        <Value>....</Value>
    </CustomProperty>
</CustomProperties>');

--your serach string

DECLARE @myVar varchar(50) = 'FieldA';

--the query

UPDATE @tbl SET CustomProperties = CAST(CAST(CustomProperties AS XML)
                                   .query('
                                            <CustomProperties>
                                            {/CustomProperties/CustomProperty[Key != sql:variable("@myVar")]}
                                            </CustomProperties>
                                         ') AS VARCHAR(1000));
SELECT * FROM @tbl;

The idea in short:

Instead of XMLDML (via .modify()) we use a simple UPDATE ... SET ... and assign a re-created XML. The XML method .query() will return a XML without the one <CustomProperty> matching the predicate.

Sql Xquery How to Replace text in Update Query

You can not assign from a xml.modify. Modify works on the variable/column directly. You can also not use modify on a cast.

You can extract the name to a xml variable, modify the xml and then put it back to the table.

declare @str varchar(200) = 'Test'
declare @xml xml

select @xml = cast(Name as xml)
from MasterTable
where ID = 18

set @xml.modify('replace value of (en-US/text())[1] with sql:variable("@Str")')

update MasterTable
set Name = cast(@xml as varchar(200))
where ID = 18

If you need this to work over more than one row at a time you can use a table variable with columns id and name where data type for name is xml instead of the @xml variable.

declare @str varchar(200) = 'Test Text'
declare @T table (ID int, Name xml)

insert into @T
select ID, cast(Name as xml)
from MasterTable
where Name is not null

update @T
set Name.modify('replace value of (en-US/text())[1] with sql:variable("@Str")')

update MasterTable
set Name = cast(T.Name as varchar(200))
from @T as T
where MasterTable.ID = T.ID

How to replace XML variable through SQL

actually I do not know what (and why) you do this. This seems to be quite complicated...

You can solve the error you get as follows:

Just paste this into an empty query window and transfer the idea into your code. The point is the second DECLARE @genderVal which is commented. Change the comments to the other DECLARE and you can reproduce it. If this is NULL you'll get the error you described. That means: check the value of your variable. It seems to be NULL.

Be aware, that I corrected the wrong closing tags "Phys" and I removed the inner "root"-tags...

declare @xml XML=
'<root>
  <Physicians>
    <name>Doctor1</name>
    <gender>M</gender>
    <langF1>
      <a title="English" href="/default.aspx?id=23">English</a>
    </langF1>
  </Physicians>
  <Physicians>
    <name>Doctor2</name>
    <gender>F</gender>
    <langF1>
      <a title="French" href="/default.aspx?id=56">French</a>
    </langF1>
  </Physicians>
</root>';
select @xml;

--This will search for the physician with the name "Doctor1"... Dont' know if you need this... Your example could be meant with one XML-for each physician...
DECLARE @ProviderName varchar(200)='Doctor1';

--DECLARE @genderVal varchar(10)=NULL;
DECLARE @genderVal varchar(10)='F';

--Update Doctor1 with new @genderVal=NULL --> The error you described
SET @xml.modify('replace value of (/root/Physicians[name=sql:variable("@ProviderName")]/gender/text())[1] with sql:variable("@genderVal")');
select @xml;

EDIT: Change the language

If I understand you correctly you don't have to bother, whether a language exists or not. You just delete it in any case

SET @xml.modify('delete (/root/Physicians[name=sql:variable("@ProviderName")]/langF1/a)[1]');
select @xml;

Now you build your inner xml for "a":

DECLARE @langTitle varchar(100)='French';
DECLARE @langLink varchar(100)='/default.aspx?id=56';
DECLARE @newLang XML='<a title="' + @langTitle + '" href="' +  @langLink + '">' + @langTitle + '</a>';

This inner xml can be inserted into an existing node:

SET @xml.modify('insert sql:variable("@newLang") into (/root/Physicians[name=sql:variable("@ProviderName")]/langF1)[1]');
select @xml;

---

Related Topics