Update xml based off datatype
Msg 227, Level 15, State 1, Line 20 "Modify" is not a valid function,
  property, or field.


XML is case sensitive and in SQL Server so are the functions used on the XML datatype. Change Modify(... to modify(....

If you have untyped XML you will still get an error.


  Msg 2356, Level 16, State 1, Line 21 XQuery
  [AllUserData.tp_ColumnSet.modify()]: The target of 'replace value of'
  must be a non-metadata attribute or an element with simple typed
  content, found 'element(nvarchar1,xdt:untyped) ?'


To fix that you need to specify the text() element of the node you want to update.

You could also make use of the exist() function in the where clause instead of extracting the value.

update dbo.AllUserData
set tp_ColumnSet.modify('replace value of (/nvarchar1/text())[1] with "1996"')
where tp_ColumnSet.exist('/nvarchar13/text()[. = "623"]') = 1;




From comment:


  The XQuery modify method is not allowed on sparse column sets.


You need to update the value using the regular column instead of the column set.

update dbo.AllUserData
set nvarchar1 = N'1996'
where nvarchar13 = N'623';