如何使用XML数据更新SQL表列

小编典典

如何使用XML数据更新SQL表列

sql

表格1：

id      title               chtml
0       Lopez, Michelle MD  <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></StartOne></root>
1       Michael, Jogn, MD   <root><StartOne><Value1>Michael, Jogn, MD</Value1><Value2>English</Value2><Value3><a title="99 show drive" href="myloc.aspx?id=05" target="_blank">99 show drive</a></Value3><Value4>908-783-0909</Value4><Value5><a title="KM" href="myspec.aspx?id=40 target="_blank">KM</a></Value5></StartOne></root>

的IDchtml类型ntext为0：

<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>
    </StartOne>
</root>

的IDchtml类型ntext为1：

<root>
    <StartOne>
        <Value1>Michael, Jogn, MD</Value1>
        <Value2>English</Value2>
        <Value3>
            <a title="99 show drive" href="myloc.aspx?id=05" target="_blank">99 show drive</a>
        </Value3>
        <Value4>908-783-0909</Value4>
        <Value5>
            <a title="KM" href="myspec.aspx?id=40 target="_blank">KM</a></Value5>
        </Value5>
    </StartOne>
</root>

Table 2并Table 3具有用于创建Table 1链接网址，标题和值的数据：

Table 2:

    id              title
    ---------------------------------
    56              49 west point
    90              130 chester lane
    12              320 nolan street
    05              99 show drive

Table 3:

    id              description
    ------------------------------
    78              CM
    39              IM
    40              KM

Table 4具有用于更新Table 1值的更新数据：

Table 4:

    Name                    Value2              Value3                  Value4              Value5
    --------------------------------------------------------------------------------------------------------------
    Lopez, Michelle MD      English             130 chester lane        908-783-0909        KM
    Michael, Jogn, MD       Italian             320 nolan street        540-029-2090        IM

我正在尝试以下查询来更新值之一：

declare @xml xml;
select @xml = cast([content_html] as xml)
from [myDB1].[dbo].[Table 1]

set @xml.modify('
  replace value of (/root/StartOne/Value3/text())[1]
  with "<a title="{Value3 from Table 4}" href="myloc.aspx?id={ID from Table 2 that matches the title with the title from Table 4}" target="_blank">{Value3 from Table 4}</a>"
');

-- How can I update the anchor link values of one table by querying another table data.
-- How can I update multiple fields, for Example `Value3` and `Value5`?

update [myDB1].[dbo].[Table 1]
set [content_html] = cast(@xml as nvarchar(max))
where [content_title] = 'Lopez, Michelle MD'

一个例子是：

declare @xml xml;
select @xml = cast([content_html] as xml)
from [myDB1].[dbo].[Table 1]

set @xml.modify('
  replace value of (/root/StartOne/Value3/text())[1]
  with "<a title="130 chester lane" href="myloc.aspx?id=90" target="_blank">130 chester lane</a>"
');

update [myDB1].[dbo].[Table 1]
set [content_html] = cast(@xml as nvarchar(max))
where [content_title] = 'Lopez, Michelle MD'

表1（上述更新后）：

id      title               chtml
0       Lopez, Michelle MD  <root><StartOne><Value1>Lopez, Michelle MD</Value1><Value2>Spanish</Value2><Value3><a title="130 chester lane" href="myloc.aspx?id=90" target="_blank">130 chester lane</a></Value3><Value4>908-783-0909</Value4><Value5><a title="CM" href="myspec.aspx?id=78" target="_blank">CM</a></Value5></StartOne></root>
1       Michael, Jogn, MD   <root><StartOne><Value1>Michael, Jogn, MD</Value1><Value2>English</Value2><Value3><a title="99 show drive" href="myloc.aspx?id=05" target="_blank">99 show drive</a></Value3><Value4>908-783-0909</Value4><Value5><a title="KM" href="myspec.aspx?id=40 target="_blank">KM</a></Value5></StartOne></root>

请为我提供以下帮助：

如何通过查询另一个表数据来更新一个表的锚链接值？
我怎样才能更新多个字段，例如Value3和Value5 ，然后运行更新语句？

阅读 172

2021-04-14

共1个答案

小编典典

这是解决你的问题的方法

declare @table4 table
(Name nvarchar(22), Value3 nvarchar(22))

    insert into @table4 values ('Lopez, Michelle MD' ,'130 chester lane')       
    insert into @table4 values ('Michael, Jogn, MD ','320 nolan street')

declare @table1 table
(id int, title nvarchar(max), chtml ntext)


insert into @table1 values (0,'Lopez, Michelle MD',  '<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></StartOne></root>')
insert into @table1 values (1,'Michael, Jogn, MD',   '<root><StartOne><Value1>Michael, Jogn, MD</Value1><Value2>English</Value2><Value3><a title="99 show drive" href="myloc.aspx?id=05" target="_blank">99 show drive</a></Value3><Value4>908-783-0909</Value4><Value5><a title="KM" href="myspec.aspx?id=40 target="_blank">KM</a></Value5></StartOne></root>')


declare @xml xml;
select top 1 @xml = cast(chtml as xml)
from @table1

-- How can I update the anchor link values of one table by querying another table data.
declare @titl nvarchar(22)
select @titl = Value3 from @table4 where Name = 'Lopez, Michelle MD'
set @xml.modify('
  replace value of (/root/StartOne/Value3/a/@title)[1]
  with sql:variable("@titl")
');
set @xml.modify('
  replace value of (/root/StartOne/Value3/a/text())[1]
  with sql:variable("@titl")
');

-- How can I update multiple fields, for Example `Value3` and `Value5`?
-- Answer: here you can modify Value5

update @table1
set chtml = cast(@xml as nvarchar(max))
where id = 0

select * from @table1

2021-04-14