SQL 使用 xml.modify 将参数插入到 xml 列的特定元素中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13234175/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use of xml.modify to insert parameters into specific element of an xml column
提问by Christian
I would like to use a stored procedure to insert some values passed in as parameters into elements in the xml of a column. I have this so far The following parameters:
我想使用存储过程将一些作为参数传入的值插入到列的 xml 中的元素中。到目前为止，我有以下参数：
@profile_id int,
@user_id nvarchar(50),
@activity_name nvarchar(50),
@display_name nvarchar(50)
Retrieve the desired xml:
检索所需的 xml：
DECLARE @profiles_xml xml
SET @profiles_xml = (SELECT profiles from tbl_applied_profiles WHERE profiles.value('(Profile/ID)[1]','int')= @profile_id)
The xml from the column inside the @profiles_xml looks like this:
@profiles_xml 中列中的 xml 如下所示：
<Profile>
<ID>20</ID>
<User>
<ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>
<Name>somename</Name>
<Activities>
<Activity>
<Name>activity1</Name>
</Activity>
</Activities>
</User>
</Profile>
Attempt to Insert into User with specific ID the activity name and display name:
尝试将活动名称和显示名称插入到具有特定 ID 的用户中：
SET @profiles_xml.modify('
insert
if(/Profile/User/ID=sql:variable("@user_id"))
then Activity[Name=sql:variable("@activity_name")][DisplayName=sql:variable("@display_name")]
else()
as first
into (/Profile/User/Activities)[1]')
I have also tried this with no success:
我也试过这个没有成功：
SET @devices_xml.modify('
insert /Profile/User[ID=sql:variable("@user_id")]/Activity[Name=sql:variable("@activity_name")][DisplayName=sql:variable("@display_name")]
into (/Profile/User/Activities)[1]')
And this:
和这个：
SET @devices_xml.modify('
insert
/Profile/User[ID=sql:variable("@user_id")]/Activities/Activity[Name=sql:variable("@activity_name")][DisplayName=sql:variable("@display_name")]
into (/Profile/User/Activities)[1]')
What is the correct way to do this?
这样做的正确方法是什么？
回答by Mikael Eriksson
declare @XML xml = '
<Profile>
<ID>20</ID>
<User>
<ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>
<Name>somename</Name>
<Activities>
<Activity>
<Name>activity1</Name>
</Activity>
</Activities>
</User>
</Profile>'
declare @user_id nvarchar(50) = '20'
declare @activity_name nvarchar(50) = 'activity1'
declare @display_name nvarchar(50) = 'displayname1'
set @xml.modify('insert <DisplayName>{sql:variable("@display_name")}</DisplayName>
into (/Profile[ID = sql:variable("@user_id")]
/User/Activities/
Activity[Name = sql:variable("@activity_name")])[1]')
Result:
结果：
<Profile>
<ID>20</ID>
<User>
<ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>
<Name>somename</Name>
<Activities>
<Activity>
<Name>activity1</Name>
<DisplayName>displayname1</DisplayName>
</Activity>
</Activities>
</User>
</Profile>
回答by podiluska
Try this
尝试这个
declare @ins xml
'<Activity><Name>'+
@activity_name+
'</Name><DisplayName>'
+@display_name+
'</DisplayName></Activity>'
SET @devices_xml.modify('
insert sql:variable("@ins") into (/Profile[ID=sql:variable("@user_id")]/User/Activities)[1]')
Or
或者
SET @devices_xml.modify('
insert <Activity>
<Name>{sql:variable("@activity_name")}</Name>
<DisplayName>{sql:variable("@display_name")}</DisplayName>
</Activity>
into (/Profile[ID=sql:variable("@user_id")]/User/Activities)[1]' )
回答by wBob
Not sure I fully understand your logic, but you can insert into xml using sql:variable with curly brackets eg
不确定我是否完全理解您的逻辑，但是您可以使用 sql:variable 和大括号将其插入到 xml 中，例如
DECLARE @profiles_xml xml
set @profiles_xml = '<Profile>
<ID>20</ID>
<User>
<ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>
<Name>somename</Name>
<Activities>
<Activity>
<Name>activity1</Name>
</Activity>
</Activities>
</User>
</Profile>'
SELECT 'before' s, DATALENGTH(@profiles_xml) dl, @profiles_xml
DECLARE @user_id CHAR(36), @activity_name NVARCHAR(MAX), @display_name NVARCHAR(MAX)
SELECT @user_id = 'BC4A18CA-AFB5-4268-BDA9-C990DAFE7783', @activity_name = 'TEST ACTIVITY NAME', @display_name = 'TEST DISPLAY NAME'
SET @profiles_xml.modify('
insert <Activity><Name>{sql:variable("@activity_name")}</Name><DisplayName>{sql:variable("@display_name")}</DisplayName></Activity>
as first
into (/Profile/User/Activities)[1]')
SELECT 'after' s, DATALENGTH(@profiles_xml) dl, @profiles_xml
