Geek Answers Handbook

Using xml.modify to insert parameters into a specific xml column element

I would like to use a stored procedure to insert some values ​​passed as parameters into elements in an xml column. I still have it. The following options:

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

Get 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 @profiles_xml is as follows:

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

Trying to insert a user name and display name into a user with a specific identifier:

 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 also tried this without 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 right way to do this?

+7
source share
3 answers
 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>
+14
source

try it

 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]' )
+1
source

Not sure if I fully understand your logic, but you can paste into xml using the sql variable: with curly braces, e.g.

 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
+1
source

More articles:


All Articles