Error when using modify and insert on XML data retrieved from table: Must be single node

Question

I am attempting to insert into some xml I am retrieving from a column in a table. I retrieve it as follows:

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 looks like this:

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

However when I try and insert an element into it like this:

SET @devices_xml.modify('insert <Activity><Name>testname</Name></Activity>
 as first into (Profiles/User/Activities[1])')

I get the following error:

XQuery [modify()]: The target of 'insert' must be a single node, found 'element(Activities,xdt:untyped) *'

I don't understand why there is an error, as there is only one Activities element currently.

Answer 1

You have two little errors:

• the top-level XML nodes is <Profile> - but you had Profiles in your insert XQuery
• and even though your collection might have no entries or just a single entry - the XPath that you specify could potentially return multiple nodes - so you need to explicitly tell it to take the first of those nodes. Here, you had the [1] indexer in the wrong place (inside the closing ) ).

Try this XQuery:

SET @devices_xml.modify('insert <Activity><Name>testname</Name></Activity>
                         as first into (Profile/User/Activities)[1]')

At least in my case, this then returned:

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