How to update certain node values from set of node values in XML type table column value in sql

Hi folks,

I have recently got task to create a SQL procedure which could update 2 specific node values of an xml column value in a table. Original column has 7 to 10 node value sets depending on the user type (in my scenario, it's employee type and student type). I'm writing this article is to put all the needed steps to one place because it will make easier for someone to find it in here rather than going through number of pages and links for each and every part.

The xml column value is some what like this,

<attributes>
  <attribute>
    <code>EMPID</code>
    <type>System.Int32</type>
    <value>0477348</value>
  </attribute>
  <attribute>
    <code>EMPEID</code>
    <type>System.Int32</type>
    <value>19245866</value>
  </attribute>
  <attribute>
    <code>EMPBADGEID</code>
    <type>System.Int32</type>
    <value>15354</value>
  </attribute>
  <attribute>
    <code>EMPCF</code>
    <type>CMB</type>
    <value>1</value>
  </attribute>
  <attribute>
    <code>ENTITY</code>
    <type>CMB</type>
    <value>4</value>
  </attribute>
  <attribute>
    <code>JOBTITLE</code>
    <type>System.String</type>
    <value>Executive Director</value>
  </attribute>
  <attribute>
    <code>DEPARTMENT</code>
    <type>CMB</type>
    <value>Transportation Parking &amp; Traffic</value>
  </attribute>
  <attribute>
    <code>DEPARTMENTLOCATOIN</code>
    <type>CMB</type>
    <value>29</value>
  </attribute>
  <attribute>
    <code>MOBILENO</code>
    <type>System.String</type>
    <value>9097300644</value>
  </attribute>
</attributes>

I have to update above green colored values with the given two values according to the given userid. The approach i used is inserting the <code> value, <type> value and <value> value to a temporary table and then update the relevant columns with the given values and then replace the original table value with the updated values in the temporary table. 

First step is to insert the node values to separate columns in a temporary table and i found how to do it via web. 

DECLARE @tempTable TABLE (
id int identity(1,1) not null,
code VARCHAR(500),
type VARCHAR(500),
value VARCHAR(500))

Since the table column is xml, I had to convert it to a varchar value.

DECLARE @xml XML
SELECT @xml= CAST(userAttributes AS VARCHAR(MAX)) FROM op_tb_trn_user WHERE userid = @userid

Then insert it to a temporary table.

INSERT INTO @tempTable
SELECT  
  Tbl.Col.value('code[1]', 'varchar(500)'),  
  Tbl.Col.value('type[1]', 'varchar(500)'),  
  Tbl.Col.value('value[1]', 'varchar(500)')
FROM   @xml.nodes('attributes/attribute') Tbl(Col)

Run a select statement on your temporary table and the output will be something like this,

id code                  type         value
1 EMPID              System.Int32 0477348
2 EMPEID             System.Int32 19245866
3 EMPBADGEID         System.Int32   15354
4 EMPCF              CMB              1
5 ENTITY             CMB              4
6 JOBTITLE           System.String Executive Director
7 DEPARTMENT         CMB              Transportation Parking & Traffic
8 DEPARTMENTLOCATOIN CMB              29
9 MOBILENO           System.String 9097300644

Then you should update the relevant column with the given values.
In my scenario,

UPDATE @tempTable SET value = @newvalue1 WHERE code = 'EMPID'
UPDATE @tempTable SET value = @newvalue2 WHERE code = 'EMPEID'

According to my scenario, i have declared set of variables to hold the values in my temporary table and then create a dynamic sql command with those variables.

DECLARE @type VARCHAR(100)
DECLARE @value VARCHAR(100)
DECLARE @type1 VARCHAR(100)
DECLARE @value1 VARCHAR(100)
DECLARE @type2 VARCHAR(100)
DECLARE @value2 VARCHAR(100)........

SET @empstring = '<attributes><attribute><code>EMPID</code><type>'+@type+'</type><value>'+@value+'</value></attribute><attribute><code>EMPEID</code><type>'+@type1+'</type><value>'+@value1+'</value></attribute><attribute><code>EMPBADGEID</code><type>'+@type2+'</type><value>'+@value2+'</value></attribute><attribute><code>EMPCF</code><type>'+@type3+'</type><value>'+@value3+'</value></attribute><attribute><code>ENTITY</code><type>'+@type4+'</type><value>'+@value4+'</value></attribute><attribute><code>JOBTITLE</code><type>'+@type5+'</type><value>'+@value5+'</value></attribute><attribute><code>DEPARTMENT</code><type>'+@type6+'</type><value>'+@value6+'</value></attribute><attribute><code>DEPARTMENTLOCATOIN</code><type>'+@type7+'</type><value>'+@value7+'</value></attribute><attribute><code>MOBILENO</code><type>'+@type8+'</type><value>'+@value8+'</value></attribute></attributes>'

Then i got another problem while i'm trying to update the original table column values with xml converted value of my dynamic sql because the dynamic sql command has '&' in somewhere in it. '&' is causing problem only because it's an special character in xml. Therefore we have to treat him separately. We have to replace '&' with '&amp;' and then only we can convert this in to xml.

SELECT @initialpart = SUBSTRING(@empstring,1,CHARINDEX('&',@empstring) -1)
SET @initialpart = @initialpart+'&amp;'
SELECT @final = SUBSTRING(@empstring,CHARINDEX('&',@empstring) + 1,len(@empstring))
SET @finalempstring = @initialpart+@final


Then you can update the original value like,

UPDATE op_tb_trn_user SET userAttributes = CAST(@finalempstring AS XML) WHERE userid = @userid

You can proceed with the update statement without the '&' convention statements if there is no '&' in your dynamic command.

Happy coding... :)