how can I update an xml by variable?

• bkshn

  Right there with Babe

  Points: 794

  More actions

  February 8, 2014 at 2:26 am

  #303946

  Hello

  I want to update my xml node by a variable, I use code bellow, but it didn't work could you help me.

  declare @urlRedirect nvarchar(4000)

  set @urlRedirect=N'www.google.com'

  declare @userxml xml

  set @userxml=N'<user>

  <information>

  <userkey>0</userkey>

  <domainname><![CDATA[192.168.0.97]]></domainname>

  <ip>192.168.0.97:2007</ip>

  <from><![CDATA[http://192.168.0.97/default.htm]]></from>

  <time>02/08/2014 08:08:57 ق.ظ</time>

  <q>https://www.google.com</q&gt;

  </information>

  <urlarea>

  <url>login.htm</url>

  </urlarea>

  <formarea>

  <user><![CDATA[sa]]></user>

  <pass><![CDATA[sa]]></pass>

  <B1><![CDATA[Submit]]></B1>

  <hascdata>false</hascdata>

  </formarea>

  <cookiearea></cookiearea>

  </user>'

  SET @Userxml.modify('replace value of (/urlarea/url/text())[1] with sql:variable("@urlRedirect")')

  select @userxml

• Eirikur Eiriksson

  SSC Guru

  Points: 182910

  More actions

  April 12, 2014 at 2:31 pm

  #1705565

  The query just has an incorrect path, this works:

  😎

  declare @urlRedirect nvarchar(4000)

  set @urlRedirect=N'www.google.com'

  declare @userxml xml

  set @userxml=N'<user>

  <information>

  <userkey>0</userkey>

  <domainname><![CDATA[192.168.0.97]]></domainname>

  <ip>192.168.0.97:2007</ip>

  <from><![CDATA[http://192.168.0.97/default.htm]]></from>

  <time>02/08/2014 08:08:57 ?.?</time>

  <q>https://www.google.com</q&gt;

  </information>

  <urlarea>

  <url>login.htm</url>

  </urlarea>

  <formarea>

  <user><![CDATA[sa]]></user>

  <pass><![CDATA[sa]]></pass>

  <B1><![CDATA[Submit]]></B1>

  <hascdata>false</hascdata>

  </formarea>

  <cookiearea></cookiearea>

  </user>'

  SET @Userxml.modify('replace value of (/user/urlarea/url/text())[1] with sql:variable("@urlRedirect")')

  select @userxml