CODEWITHSUNDEEP
sql-serverxmlsql-server-2005sqlxml
the sandman
the sandman

Reputation: 1029

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

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

Upvotes: 0

Views: 1730

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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.

Upvotes: 1

Related Questions