1

In SQL Server 2008, I would like to insert some elements and values into a specific child element of an xml column based on the value of another of that element's children (ID in this case). The xml currently looks like this: 

<Profile>
  <ID>16</ID>
  <User>
    <ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>
    <Name>test</Name>
    <Activities />
  </User>
</Profile>

However, there could potentially be multiple <User> elements within the xml, and I need to insert only into the <User> of a specific <ID> value. How can I achieve this in MS SQL Server 2008/ t-sql?

Edit: To simplify, I am grabbing the xml from that column and setting it to a variable:

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

The resulting xml value of @profiles_xml looks like what I have above.

Michael Irigoyen
  • 22,513
  • 17
  • 89
  • 131
Christian
  • 1,685
  • 9
  • 28
  • 48
  • Mikael Eriksson posted an answer that addresses this question on a similar question I asked, the answer is here: https://stackoverflow.com/questions/13234175/use-of-xml-modify-to-insert-parameters-into-specific-element-of-an-xml-column – Christian Nov 06 '12 at 16:28

0 Answers0