Read /Write in XML column in sqlserver

This is simple example to read and write in xml column of a table in sqlserver . You can directly execute in sqlserver too.


----------------- To insert/update in xml column
declare @t table (id int, xmlCol xml)

insert into @t values(1,'14747')

select * from @t;

update @t set xmlcol = '1474714749' where id =1

select * from @t

--even you cast as
select cast ( xmlcol as varchar(max)) , * from @t

----------------- Select example-----1st example --
Declare @responce xml; DECLARE @douvalue varchar(100);
SELECT @responce = ' 60.4655'
select @douvalue = @responce.value('(/double)[1]', 'varchar(100)');
select @responce, @douvalue AS DownloadEnvironment

--Declare @responce xml;
SELECT @responce = '
60.4655'

;WITH XMLNAMESPACES(DEFAULT 'http://www.webserviceX.NET/')
select @douvalue = @responce.value('/double[1]', 'varchar(100)');

select @douvalue

------2nd example - To retrieve value ---
declare @xml xml 
set @xml = '14747  14749  14750  14752'
    SELECT
        xmlnodes.idnode.value('.', 'int')
    from   
        @xml.nodes('/root/id') as xmlnodes(idnode) 

--3rd example    -- even you compare with other table too -----
   SELECT
        a.activityID, a.columnName,
        convert(varchar(12), a.createdDate,13) as createdDate ,
        CONVERT(varchar(15),CAST(a.createdDate AS TIME), 100) as activityTime     
   from   
        @xml.nodes('/root/id') as xmlnodes(idnode) 
        JOIN Activity a 
            ON   xmlnodes.idnode.value('.', 'int') = a.activityID 
   WHERE
        a.active = '1' AND
        (a.activityType = 'Viewed' OR a.activityType = 'Edited')  
   ORDER BY
        a.createdDate desc,
        a.activityTime desc