Use of xml.modify to insert parameters into specific element of an xml column

Question

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)

Retrieve the desired 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:

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

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?

When you say "with no success" do you mean a) That it is entirely unchanged, b) That a change happens but not where you want it to, or c) That an error message is displayed (or d) something else I've not though of)? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 5, 2012 at 14:39

Mikael Eriksson · Accepted Answer · 2012-11-05 14:40:19Z

23

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>

answered Nov 5, 2012 at 14:40

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Christian Over a year ago

Ah! that did the trick, thanks again! sorry for all the xml/xquery questions, I just have been trouble finding straight forward documentation on how to use it with tsql.

Mikael Eriksson Over a year ago

@Christian You can have a look at Stairway to XML.

Si8 Over a year ago

What is <DisplayName /> was already there. Would it still work? stackoverflow.com/questions/32253235/…

podiluska · Accepted Answer · 2012-11-05 14:43:25Z

1

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

edited Nov 5, 2012 at 14:43

answered Nov 5, 2012 at 14:38

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Comments

wBob · Accepted Answer · 2012-11-05 14:52:27Z

Not sure I fully understand your logic, but you can insert into xml using sql:variable with curly brackets eg

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

Collectives™ on Stack Overflow

Use of xml.modify to insert parameters into specific element of an xml column

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related