使用 xml.modify 将参数插入到 xml 列的特定元素中 [英] Use of xml.modify to insert parameters into specific element of an xml column

问题描述

我想使用存储过程将一些作为参数传入的值插入到列的 xml 中的元素中.到目前为止我有这个以下参数:

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:

@profile_id int,
@user_id nvarchar(50),
@activity_name nvarchar(50),
@display_name nvarchar(50)

检索所需的 xml:

DECLARE @profiles_xml xml
SET @profiles_xml = (SELECT profiles from tbl_applied_profiles WHERE profiles.value('(Profile/ID)[1]','int')= @profile_id)

@profiles_xml 中列中的 xml 如下所示:

The xml from the column inside the @profiles_xml looks like this:

<Profile>
  <ID>20</ID>
  <User>
    <ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>
    <Name>somename</Name>
    <Activities>
      <Activity>
         <Name>activity1</Name>
      </Activity>
    </Activities>
  </User>
</Profile>

尝试将活动名称和显示名称插入到具有特定 ID 的用户中:

Attempt to Insert into User with specific ID the activity name and display name:

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

还有这个:

 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?

推荐答案

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

结果:

<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>

这篇关于使用 xml.modify 将参数插入到 xml 列的特定元素中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！