如何使用IF/ELSE语句在Sql中更新或创建新的xml节点条目 [英] How to use IF/ELSE statement to update or create new xml node entry in Sql

问题描述

示例:

<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(目前只查看锚链接是否已经存在并更新):

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

推荐答案

尝试先删除锚元素，再插入新的.对于删除语句，它是否存在并不重要.我还提供了一种更好的方法来构建您的新锚元素.它负责为 & 等字符创建实体.

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]');

这篇关于如何使用IF/ELSE语句在Sql中更新或创建新的xml节点条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！