CODEWITHSUNDEEP
sqlsql-serverxml
Rick
Rick

Reputation: 1559

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

I am trying to update XML data column in a table based on case. Below is my code:

UPDATE #temp
SET xml_data = CASE
                  WHEN @type = 'G' 
                     THEN xml_data.modify('insert <type>G</type> after (/Main/name)[1]');
                  WHEN @type = 'Q' 
                     THEN xml_data.modify('insert <type>Q</type> after (/Main/name)[1]'); 
               END

I am getting an error:

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

Desired output:

XML structure:

<Main>
    <name>John doe</name>
    <type>Q</type>
    <age>15</age>
</Main>

Any help ?!

UPDATE:

My edited query:

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

This query is adding the type to the end of the XML. Output:

<Main>
    <name>John doe</name>
    <age>15</age>
</Main>
<Type>Q</Type>

Upvotes: 3

Views: 7394

Answers (2)

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

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

Upvotes: 7

Marc.Adans
Marc.Adans

Reputation: 213

Try this:

UPDATE #temp
   SET xml_data = 'insert <type>' + @type + '</type> into (/Main)[1]'

Sample:

DECLARE @myXML XML = 
N'<Main>
    <name>John doe</name>    
    <age>15</age>
  </Main>'  ;       

SET @myXML.modify('insert <type>Q</type> into (/Main)[1]') ;  

SELECT @myXML;

Upvotes: 1

Related Questions