Skip to main content
edited tags
Link
Mikael Eriksson
  • 22.2k
  • 5
  • 61
  • 104
added 15 characters in body
Source Link

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?

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

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?

edited title
Link

Update xml based off datatype and not tags

Source Link
Loading