Update XML in SQL Server 2005 w/out Node causes Null Error

Question

So i have a script to update/Insert the XML value of the following Node to True:

<Submitted>False</Submitted>

The issue is not all rows will contain the Node and because of this, it throws the error: "Mutator 'modify()' on '@temp' cannot be called on a null value."

What do i need to do to filter out the rows which do not contain the "Submitted" Node within the XML?

**Note, i have all these crazy CASTS because the column type is TEXT and cannot be changed because the client originally set it up that way.

DECLARE @temp XML

SELECT 
     @temp = CAST(CAST(TicorOregon..tbl_Module_RequestForms_Items.XML AS NTEXT) AS XML) 
FROM 
     TicorOregon..tbl_Module_RequestForms_Items
WHERE
 CAST(CAST(TicorOregon..tbl_Module_RequestForms_Items.XML AS NTEXT) AS XML).value('(//Record/Submitted)[1]', 'NVARCHAR(max)') <> 'True'

-- modification to local XML var
SET 
   @temp.modify('replace value of (//Record/Submitted[1]/text())[1] with "True"') 

-- write it back into the table as TEXT column      
UPDATE 
   TicorOregon..tbl_Module_RequestForms_Items
SET 
   XML = CAST(CAST(@temp AS VARCHAR(MAX)) AS TEXT)
WHERE
 CAST(CAST(TicorOregon..tbl_Module_RequestForms_Items.XML AS NTEXT) AS XML).value('(//Record/Submitted)[1]', 'NVARCHAR(max)') <> 'True'
AND  CAST(CAST(TicorOregon..tbl_Module_RequestForms_Items.XML AS NTEXT) AS XML).value('(//Record/Submitted)[1]', 'NVARCHAR(max)') <> null

Thanks. It's the best i could do with this damn column being TEXT and the data XML with no way of changing the column type thanks to the customer who set this up. — the sandman
– the sandman, Commented Dec 7, 2011 at 0:43
Think i need to add an IF EXISTS statement, working on this now — the sandman
– the sandman, Commented Dec 7, 2011 at 1:07

Mikael Eriksson · Accepted Answer · 2011-12-07 07:15:17Z

1

Test your XML variable for null before trying to update.

if @temp is not null
begin
  -- modification to local XML var
  SET @temp.modify ----

  -- write it back into the table as TEXT column
  SET @temp.modify....
end

Note: You might have trouble with this code if there are more than one row having <Submitted>False</Submitted>. You will have the XML from one row in @temp (probably the last one according to some index) but you will update all rows where <Submitted>False</Submitted> with that XML.

answered Dec 7, 2011 at 7:15

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

the sandman Over a year ago

just what i needed. YOU ARE DA MAN!

Collectives™ on Stack Overflow

Update XML in SQL Server 2005 w/out Node causes Null Error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related