sql - Sql中如何使用IF/ELSE语句更新或新建xml节点条目

Question

例子:

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

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

如果 anchor 链接已经存在，我该如何修改它，更新。否则用 < 创建一个新的 anchor 链接和 >而不是 <和 >

更新:这对我有用(不确定是否有更有效的方法)

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

Answer 1

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

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