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 lookin 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?