Coding Geek

T-SQL : Accessing & Modifying Node Value of XML Column

 Its flexible, while designing Database to have some fields in XML. It allows to store multiple values/structure which can be easily extended whereas in case of simple table you will need to add new column(s) and then change the data access layer etc.

XML column can be easily manipulated using the TSQL. we can select the particular node value(s) in the form of column(s), update them as well as apply them in where clause;

Here is the way how to select particular attribute value in the form of column;

Select CAST(Variables as XML).value('(/Variables/Variable[@name="OrderTakenBy"]/@value)[1]', 'varchar(500)') As OrderTakenBy
from tblVariables

Similarly, we can filters the records based on a particular attribute of xml column

Select VariableName
from tblVariables
WHERE CAST(Variables as XML).value('(/Variables/Variable[@name="OrderTakenBy"]/@value)[1]', 'varchar(500)') = 'Nadeem'

Following script shows how to update a particular attribute value with a fixed value;

UPDATE      Vars
SET         CAST(VARIABLES as XML).modify('replace value of (/Variables/Variable[@name=''OrderTakenBy"])[1]/@value with "Nadeem"')
FROM        tblVariables AS Vars

Moreover, you can update the attribute value with different values from other table

UPDATE      Vars
SET         CAST(VARIABLES as XML).modify('replace value of (/Variables/Variable[@name=''OrderTakenBy"])[1]/@value with sql:column("O.Name")')
FROM        tblVariables AS Vars INNER JOIN Orders O
ON O.pkey = Vars.kOrder