本文介紹了如何使用 XML 數據更新 SQL 表列的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
限時送ChatGPT賬號..
表 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>
chtml 是 ntext 類型，id 0:
The chtml is of type ntext for id 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>
chtml 是 ntext 類型，id 1:
The chtml is of type ntext for id 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 包含用于為鏈接 Url、標題和值創建 Table 1 的數據:
Table 2 and Table 3 has data which is used in the creation of Table 1 for the link Url, title, and value:
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 has the updated data which will be used to update Table 1 values:
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
我正在嘗試使用以下查詢來更新其中一個值:
I am trying the following query to update one of the value:
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'
表一(以上更新后):
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>
請幫助我解決以下問題:
Please help me with the following:
- 如何通過查詢更新一張表的錨鏈接值另一個表數據?
- 如何更新多個字段，例如
Value3和Value5然后運行更新語句?
- How can I update the anchor link values of one table by querying another table data?
- How can I update multiple fields, for Example
Value3andValue5and then run the update statement?
推薦答案
這是解決問題的方法
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
這篇關于如何使用 XML 數據更新 SQL 表列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！