Read and Update XML Node of XML datatype in SQL Server

Select Node value

This is a simple explanation on how to select a particular node from xml data type of a record in SQL server.

Sample XML

  00003479
  Brodie's HomeSton
  NULL
  
  
  AU941170
  2010-07-22T09:42:20
  vcAuditorDesc,chUpdateStaffCode,dtUpdateDateTime,dtUpdateDateTime

So what I want to do is basically to get the value of ChangedColumns node

select  xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
from tblReferenceHistory
WHERE intReferenceHistoryID = 125

Update Existing Node value

Sample XML

  00003479
  Brodie's HomeSton
  NULL
  
  
  AU941170
  2010-07-22T09:42:20
  

Changed column has no value in it and you want to update it. Basically you can’t edit the existing node value so what you need to do is to readd it and remove the first instance of that node

DECLARE @ChangedColumns VARCHAR(255)

SELECT @ChangedColumns = xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
FROM tblReferenceHistory
WHERE intReferenceHistoryID = 125

-- first update - add a new ... node
UPDATE tblReferenceHistory
SET xmlDetail.modify('insert {sql:variable("@ChangedColumns")} as last into (/iReference)[1]')
WHERE intReferenceHistoryID = 124

-- second update - remove the empty  node
UPDATE dbo.tblReferenceHistory
SET xmlDetail.modify('delete (/iReference[1]/ChangedColumns[1])[1]')
WHERE intReferenceHistoryID = 124

SELECT xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
FROM tblReferenceHistory
WHERE intReferenceHistoryID = 124