Read And write xml data in xml column in sqlserver

Suppose I have an entry


assetsIDs_xml  '119'

update Designs set assetsIDs_xml = ''
where ( assetsIDs_xml.exist('/root/id[text()=119]') =1 ) 


SELECT
           Columnlist
      FROM
            tableName c
      WHERE
            AND ((c.userIDs_xml.exist('/root/id[text()=sql:variable("@userID")]') = 1)  

Comments

Post a Comment

Popular posts from this blog

IIS not work, Give error "HRESULT...." or When IIS start gives error "Already running a process on this port"

Hello All, In my pc, IIS was working fine as I know. Today when I want to run my site from IIS. It gives me above error . I am searching lot for my this issue. Even I was did stop my firewall , still getting the same. So I googling and get solution that skype is also share the same port of IIS : 80. So follow below to solve this issue. The cmd NETSTAT -ano showed Skype (via the PID) was using port 80. This was preventing IIS starting a local website with the message: The process cannot access the file because it is being used by another process. (Exception from HRESULT: 0x80070020) This was resolved in Skype by unchecking T ools > Options... > Advanced > Connection > "Use port 80 and 443 as alternatives for incoming connections" Refer link :Prevent Skype using port 80 and causing IIS to throw a HRESULT: 0x80070020 Exception
Read more

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 = ' 14747 14749 ' 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...
Read more