how can I update an xml by variable?

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply