SQL将的值替换为始终随机的xml代码

Question

我在一个表的几行中有下面的SQL (表在configuration_xml中是tbldatafeed列)。所有的UserName=""和Password=""对于每一行都是不同的，并且不会重复，所以我无法找到/替换它。我正在尝试编写一个查询来查找所有这些内容，并将它们替换为我选择的用户名/密码。

<DataFeed xmlns="http://www.tech.com/datafeed/dfx/2010/04" xmlns:plugin="pluginExtensions" Type="TODO" Guid="TODO" UserAccount="DF_LEAN_PopulateCommentsSubForm" Locale="en-US" DateFormat="" ThousandSeparator="" NegativeSymbol="" DecimalSymbol="" SendingNotifications="false" SendJobStatusNotifications="false" RecipientUserIds="" RecipientGroupIds="" RecipientEmailAddresses="" Name="CI_C11.01_Lean-Lean_Reject Comments_A2A" >
  <Transporter>
    <transporters:ArcherWebServiceTransportActivity xmlns:transporters="clr-namespace:ArcherTech.DataFeed.Activities.Transporters;assembly=ArcherTech.DataFeed" xmlns:out="clr-namespace:ArcherTech.DataFeed;assembly=ArcherTech.DataFeed" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:compModel="clr-namespace:ArcherTech.DataFeed.ComponentModel;assembly=ArcherTech.DataFeed" xmlns:channel="clr-namespace:ArcherTech.DataFeed.Engine.Channel;assembly=ArcherTech.DataFeed" xmlns:engine="clr-namespace:ArcherTech.DataFeed.Engine;assembly=ArcherTech.DataFeed" xmlns:kernel="clr-namespace:ArcherTech.Kernel.Channel;assembly=ArcherTech.Kernel" xmlns="clr-namespace:ArcherTech.DataFeed;assembly=ArcherTech.DataFeed" xmlns:schema="clr-namespace:System.Xml.Schema;assembly=System.Xml" xmlns:xmlLinq="clr-namespace:System.Xml.Linq;assembly=System.Xml" xmlns:domain="clr-namespace:ArcherTech.Common.Domain;assembly=ArcherTech.Common" xmlns:s="clr-namespace:System;assembly=mscorlib" x:Key="transportActivity" SearchType="ReportId" Uri="https://arcs-d" RecordsPerFile="100" ReportID="EC514865-88D5-49CE-A200-7769EC1C2A88" UseWindowsAuth="false" IsWindowsAuthSpecific="false" WindowsAuthUserName="i9XzCczAQ7J2rHwkg6wG9QF8+O9NCYJZP6y5Kzw4be0+cdvUaGu/9+rHuLstU736pnQrRcwmnSIhd6oPKIvnLA==" WindowsAuthPassword="+y0tCAKysxEMSGv1unpHxfg6WjH5XWylgP45P5MLRdQ6+zAdOLSVy7s3KJa3+9j2i83qn8I8K7+1+QBlCJT1E7sLQHWRFOCEdJgXaIr1gWfUEO+7kjuJnZcIEKZJa2wHyqc2Z08J2SKfdCLh7HoLtg==" WindowsAuthDomain="" ProxyName="" ProxyPort="8080" ProxyUsername="" ProxyPassword="" ProxyDomain="" IsProxyActive="False" ProxyOption="None" InstanceName="ARCS-D" TempFileOnSuccessAction="DoNothing" TempFileOnSuccessRenameString="" TempFileOnErrorAction="DoNothing" TempFileOnErrorRenameString="" Transform="{engine:DataFeedBinding Path=Transform}" SessionContext="{engine:DataFeedBinding Path=Session}">
      <transporters:ArcherWebServiceTransportActivity.Credentials>
        <NetworkCredentialWrapper UserName="TeSZmI1SqO0eJ0G2nDVU+glFg/9eZfeMppYQnPfbeg8=" Password="Slt4VHqjkYscWyCwZK40QJ7KOQroG9OTKr+RGt9bQjE=" />
      </transporters:ArcherWebServiceTransportActivity.Credentials>
    </transporters:ArcherWebServiceTransportActivity>
  </Transporter> 
</DataFeed>

我需要能够设置一个值并将其替换为一个查询

我写了以下内容

select @config_xml=configuration_xml from bldatafeed where datafeed_name = 'REMOVED'
update tbldatafeed set configuration_xml.modify(//*:NetworkCredentialWrapper/@UserName)[1] with "abc" ')
where datafeed_name = 'REMOVED'

这样做是可行的，但只有当我在每个区域中每次都设置"abc“密码，并且在某些情况下我对50+行运行此密码时，它才有效。

我也试过了：

Declare @server nvarchar(max) = 'abc'
Declare @config_xml xml
select @config_xml=configuration_xml from bldatafeed where datafeed_name = 'REMOVED'
update tbldatafeed set configuration_xml.modify(//*:NetworkCredentialWrapper/@UserName)[1] with @server ')
where datafeed_name = 'REMOVED'

由此产生的错误是: XQuery tbldatafeed.configuration_xml.modify()：不支持顶级属性节点

我想要做的是设置我的变量，并利用它，因为我将为多行设置这个变量，不幸的是，这个错误使得这个问题很难解决。

谢谢你的帮助，这让我有点困惑。

Answer 1

使用函数sql:variable()在XQuery表达式中使用变量。

declare @T table(X xml);
insert into @T values('<X UserName=""/>');

declare @UserName nvarchar(max) = 'abc'

update @T set
X.modify('replace value of (/X/@UserName)[1] 
           with sql:variable("@UserName")');