Update xml based off datatype

Question

I need to update a value in my xml datatype column, currently I am able to pull specific values using

Select AllUserData.tp_ColumnSet.value('nvarchar13[1]', 'varchar(max)') AS AssetId...

My data in the column looks like this, because this is from Sharepoint and I cannot modify the xml format in any way.

<bit1>0</bit1>
<nvarchar1>1995</nvarchar1>
<nvarchar3>Ford</nvarchar3>
<nvarchar5>White</nvarchar5>
<nvarchar6>Van</nvarchar6>
<nvarchar7>123456789</nvarchar7>
<nvarchar9>1234</nvarchar9>
<nvarchar13>623</nvarchar13>
<nvarchar20>Company</nvarchar20>
<nvarchar22>Dept</nvarchar22>
<nvarchar23>Section</nvarchar23>
<nvarchar24>User</nvarchar24>

I have tried to piece together a statement from other questions much like the example with no success and no working where clause as shown below.

update AllUserData set tp_ColumnSet.Modify('replace value of (nvarchar1)[1] with "1996"') where AllUserData.tp_ColumnSet.value('nvarchar13[1]', 'varchar(max)') = 623

How can I modify the the "Year" by designating what AssetID it is with a where clause?

Answer 1

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