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?
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>
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]' )
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With