How to use IF/ELSE statement to update or create new xml node entry in Sql

Question

Example:

<root>
    <StartOne>
        <Value1>Lopez, Michelle MD</Value1>
        <Value2>Spanish</Value2>
        <Value3>
            <a title="49 west point" href="myloc.aspx?id=56" target="_blank">49 west point</a>
        </Value3>
        <Value4>908-783-0909</Value4>
        <Value5>
            <a title="CM" href="myspec.aspx?id=78" target="_blank">CM</a>
        </Value5>
        <Value6 /> /* No anchor link exist, but I would like to add the same format as Value5 */
    </StartOne>
</root>

Sql (currently only sees if the anchor link already exist and updates):

BEGIN
    SET NOCOUNT ON;

    --Declare @xml xml;
    Select @xml = cast([content_html] as xml)
    From [Db1].[dbo].[zTable]

    Declare @locID varchar(200);
    Declare @locTitle varchar(200);
    Declare @locUrl varchar(255);

    Select @locID = t1.content_id From [westmedWebDB-bk].[dbo].[zTempLocationTable] t1
    INNER JOIN [Db1].[dbo].[zTableFromData] t2 On t2.Value3 = t1.content_title
    Where t2.Value1 = @ProviderName --@ProviderName is a parameter

    Select @locTitle = t1.content_title From [Db1].[dbo].[zTempLocationTable] t1
    Where @locID = t1.content_id

    Set @locUrl = 'theloc.aspx?id=' + @locID + '';

    --if Value5 has text inside...

    Set @xml.modify('replace value of (/root/StartOne/Value5/a/text())[1] with sql:variable("@locTitle")');
    Set @xml.modify('replace value of (/root/StartOne/Value5/a/@title)[1] with sql:variable("@locTitle")');
    Set @xml.modify('replace value of (/root/StartOne/Value56/a/@href)[1] with sql:variable("@locUrl")');

     --otherwise... create a new anchor

     set @locAnchor = ('<a href="theloc.aspx?id=' + @locID + '" title="' + @locTitle + '">' + @locTitle + '</a>');

     set @xml.modify('replace value of (/root/StartOne/Value1/text())[1] with sql:variable("@locAnchor")'); --this adds "&lt;" and "&gt;" instead of "<" and ">" is the issue

    Update [Db1].[dbo].[zTable]
    Set [content_html] = cast(@xml as nvarchar(max))
    Where [content_title] = @ProviderName --@ProviderName is a parameter
END

How can I modify it so, if the anchor link already exist, update. Otherwise create a new anchor link with the < and > instead of < and >

Update: This is working for me now (Not sure if there is a more efficient method)

If @xml.exist('/root/StartOne/Value6/a/text()') = 1 --if there is an anchor link/text in the node
    BEGIN
        --modify the text of the link
        Set @xml.modify('replace value of (/root/StartOne/Value6/a/text())[1] with sql:variable("@locTitle")');

        --modify the title of the link
        Set @xml.modify('replace value of (/root/StartOne/Value6/a/@title)[1] with sql:variable("@locTitle")');

        --modify the url of the link
        Set @xml.modify('replace value of (/root/StartOne/Value6/a/@href)[1] with sql:variable("@locUrl")');
    END
Else --otherwise create a new anchor link
    BEGIN
        --Set @locAnchor = ('<a href="theloc.aspx?id=' + @locID + '" title="' + @locTitle + '">' + @locTitle + '</a>');

        --Set @xml.modify('insert <a title="Value6" href="Value6.aspx?id=78" target="_blank">Value6</a> into (/root/StartOne/Value6)[1]');
        declare @a  xml;
        Set @a = N'<a title="' + @locTitle+ '" href="' +@locUrl+ '" target="_blank">'+@locTitle+'</a>';
        Set @xml.modify('insert sql:variable("@a") into (/root/StartOne/Value6)[1]');
    END

yes kool it seems fine :)

wiretext
– wiretext

2015-08-28 05:39:12 +00:00
Commented Aug 28, 2015 at 5:39 — wiretext
– wiretext, Commented Aug 28, 2015 at 5:39

Mikael Eriksson · Accepted Answer · 2015-08-28 06:02:28Z

3

Try to delete the anchor element first and then insert the new one. It does not matter if it is there or not for the delete statement. I also provided a better way to build your new anchor element. It takes care of creating entities for characters like &.

-- Delete the anchor node from the XML
set @xml.modify('delete /root/StartOne/Value6/a');

-- Build the XML for the new anchor node
set @a = (
         select @locTitle as 'a/@title',
                @locUrl as 'a/@href',
                '_blank' as 'a/@target',
                @locTitle as 'a'
         for xml path(''), type
         );

-- Insert the new anchor node
set @xml.modify('insert sql:variable("@a") into (/root/StartOne/Value6)[1]');

answered Aug 28, 2015 at 6:02

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Si8 Over a year ago

What should a be declared as? Thank you.

Mikael Eriksson Over a year ago

@SiKni8 It should be XML

Si8 Over a year ago

Just figured it out but you beat me to it. Thank you. This is much simpler actually.

Si8 Over a year ago

I guess this will eliminate the need for if/else statement? Just have the above which should handle if there is Anchor that exist or if it doesn't. Will the delete give an error if nothing exist or just go to the next code?

Si8 Over a year ago

This actually worked great so far. Thank you. I +1 and accepted your answer because it was the easiest solution to implement. Thanks again.

|

wiretext · Accepted Answer · 2015-08-27 19:56:29Z

1

i hope this will help you

Declare @locUrl varchar(255);
Set @locUrl = 'xyz.aspx?id=' + '444' + '';

declare @xml xml;
set @xml = '<root>
    <StartOne>
        <Value1>Lopez, Michelle MD</Value1>
        <Value2>Spanish</Value2>
        <Value3>
            <a title="49 west point" href="myloc.aspx?id=56" target="_blank">49 west point</a>
        </Value3>
        <Value4>908-783-0909</Value4>
        <Value5>
            <a title="CM" href="myspec.aspx?id=78" target="_blank">CM</a>
        </Value5>
        <Value6>
        </Value6>
    </StartOne>
</root>';

declare @chk nvarchar(max);
-- here implementing for Value6
set @chk = (select 
C.value('(Value6/a/text())[1]', 'nvarchar(max)') col
from
@xml.nodes('/root/StartOne') as X(C))
-- make sure here 
select @chk;


if @chk is null
begin
-- INSERT
SET @xml.modify('       
insert <a title="Value6" href="Value6.aspx?id=78" target="_blank">Value6</a> 
into (/root/StartOne/Value6)[1]') 
end

else
begin
-- UPDATE
Set @xml.modify('replace value of (/root/StartOne/Value6/a/@href)[1] with sql:variable("@locUrl")');
end

select @xml

UPDATE: after your below comment this is the way to update dynamically

Declare @locUrl nvarchar(255);
Set @locUrl = 'xyz.aspx?id=' + '444' + '';

declare @xml xml;
set @xml = '<root>
    <StartOne>
        <Value1>Lopez, Michelle MD</Value1>
        <Value2>Spanish</Value2>
        <Value3>
            <a title="49 west point" href="myloc.aspx?id=56" target="_blank">49 west point</a>
        </Value3>
        <Value4>908-783-0909</Value4>
        <Value5>
            <a title="CM" href="myspec.aspx?id=78" target="_blank">CM</a>
        </Value5>
        <Value6>
        </Value6>
    </StartOne>
</root>';

declare @a  xml;
set @a = N'<a title="' + @locUrl+ '" href="' +@locUrl+ '" target="_blank">'+@locUrl+'</a>';

SET @xml.modify
('insert sql:variable("@a")
into (/root/StartOne/Value6)[1]');

select @xml;

edited Aug 27, 2015 at 19:56

answered Aug 27, 2015 at 18:16

wiretext

3,35216 silver badges19 bronze badges

6 Comments

Si8 Over a year ago

Trying to use variable for the INSERT statement here:

SET @xml.modify('Insert <a title="sql:variable("@locTitle")" href="sql:variable("@locUrl")" target="_blank">sql:variable("@locTitle")</a> Into (/root/StartOne/Value6)[1]') ;

but it's not working for me. Error: XQuery [modify()]: Syntax error near 'a'

Si8 Over a year ago

This didn't work either:

Set @locAnchor = ('<a href="theloc.aspx?id=' + @locID + '" title="' + @locTitle + '">' + @locTitle + '</a>'); Set @xml.modify('insert sql:variable("@locAnchor") into (/root/StartOne/Value6)[1]');

Error: XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string ?".

Si8 Over a year ago

Set @xml.modify('insert xs:string(sql:variable("@locAnchor")) into (/root/StartOne/Value6)[1]'); didn't work either.

wiretext Over a year ago

@SiKni8 SORRY for delay please check updated answer :)

Si8 Over a year ago

Thank you. How can I clear out the stuff inside Value6 before inserting (If it exists)? I am sorry to ask so many questions but I am starting this out. If I run it multiple times, it will just keep appending. I would like it to clear out anything prior to inserting.

|

Sam CD · Accepted Answer · 2015-08-27 20:18:12Z

1

You might want to just try replacing:

set @locAnchor = ('<a href="theloc.aspx?id=' + 
@locID + '" title="' + @locTitle + '">' + 
@locTitle + '</a>');

with:

SELECT @locAnchor = (SELECT 'theloc.aspx?id=' + @locID AS 'Value6/a/@href',
@locTitle AS 'Value6/a/@title',
@locTitle AS 'Value6/a'
FOR XML PATH (''))

instead of trying to dynamically create the XML

answered Aug 27, 2015 at 20:18

Sam CD

2,1072 gold badges20 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to use IF/ELSE statement to update or create new xml node entry in Sql

3 Answers 3

7 Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related