如何使用IF/ Sql语句在Sql中更新或创建新的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 (当前只查看锚链接是否已经存在和更新)：

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

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

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

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

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

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

Answer 2

我希望这能帮到你

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

更新:在下面的评论之后，这是动态更新的方法

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;

Answer 3

您可能想试着替换：

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

通过以下方式：

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

而不是尝试动态创建XML