modify() Top-level attribute nodes are not supported Error

[BradR]

I'm trying to return the following:

<person>
<personal_info>
...
</personal_info>
<prof_quals>
...
</prof_quals>
</person>

The following results in an error:

XQuery [#xPerson.xPerson.modify()]: Top-level attribute nodes are not
supported

DECLARE @xPerson XML

SET @xPerson = (
SELECT first_name,middle_name,last_name,maiden_name,
LTRIM(RTRIM(addr_1+' '+addr_2+' '+addr_3+' '+addr_4)) address_line,
city municipality,
isnull(state_code,province_code) region,postal_code,
country_code,'PC' passport_code,
'652-78-9658' social_security_num,
isnull(birth_month,'00')+'/'+isnull(birth_day,'00')
+'/'+isnull(birth_year,'0000') birth_date,
gender
FROM peo_master_vw c
JOIN peo_address d ON c.person_id = d.person_id
WHERE c.person_id = 8008723 AND
d.addr_key = 1
FOR XML path('personal_info'), root('person'),type)


SELECT @xPerson xPerson INTO #xPerson

DECLARE @xProfQuals XML
SET @xProfQuals =
(SELECT isnull(lic_no,cert_code) license_number,
'Unknown' licensing_agency,
'XX' state_issued,
cert_year start_date,
NULL end_date FROM peo_prof_quals
WHERE person_id = 8008723
FOR XML path('prof_quals'),type)

SELECT @xProfQuals

UPDATE #xPerson SET xPerson.modify('
insert @xProfQuals
after (//*/personal_info)')

[BradR]

Post by BradR
<person>
<personal_info>
...
</personal_info>
<prof_quals>
...
</prof_quals>
</person>

I thought I'd simplify this down to just the XML:


DECLARE @xPerson XML, @xUpdate XML

SET @xPerson = ('
<person>
<personal_info>
<first_name>Some</first_name>
<last_name>Guy</last_name>
</personal_info>
</person>'
)

SET @xUpdate = ('
<prof_quals>
<license_number>ADM</license_number>
<licensing_agency>Unknown</licensing_agency>
<state_issued>XX</state_issued>
<start_date>1983</start_date>
</prof_quals>'
)


--XQuery [#xPerson.xPerson.modify()]: Top-level attribute nodes are
not supported


SET @xPerson.modify('
insert @xUpdate
after (/person/personal_info[1])')

-- XQuery [#xPerson.xPerson.modify()]: The target of 'insert' must be
a single node, found 'element(personal_info,xdt:untyped) *'

SET @xPerson.modify('
insert
<prof_quals>
<license_number>ADM</license_number>
<licensing_agency>Unknown</licensing_agency>
<state_issued>XX</state_issued>
<start_date>1983</start_date>
</prof_quals>
after (/person/personal_info[1])')

[BradR]

Post by BradR

Post by BradR
<person>
<personal_info>
...
</personal_info>
<prof_quals>
...
</prof_quals>
</person>

<person>
<personal_info>
<first_name>Some</first_name>
<last_name>Guy</last_name>
</personal_info>
</person>'
)
<prof_quals>
<license_number>ADM</license_number>
<licensing_agency>Unknown</licensing_agency>
<state_issued>XX</state_issued>
<start_date>1983</start_date>
</prof_quals>'
)
--XQuery [#xPerson.xPerson.modify()]: Top-level attribute nodes are
not supported
after (/person/personal_info[1])')
-- XQuery [#xPerson.xPerson.modify()]: The target of 'insert' must be
a single node, found 'element(personal_info,xdt:untyped) *'
insert
<prof_quals>
<license_number>ADM</license_number>
<licensing_agency>Unknown</licensing_agency>
<state_issued>XX</state_issued>
<start_date>1983</start_date>
</prof_quals>
after (/person/personal_info[1])')

Here's where I'm I'm at:

This works (moved the [1]):

SET @xPerson.modify('
insert
<prof_quals>
<license_number>ADM</license_number>
<licensing_agency>Unknown</licensing_agency>
<state_issued>XX</state_issued>
<start_date>1983</start_date>
</prof_quals>
after (/person/personal_info)[1]')

but even if I use the same xpath, doing:

SET @xPerson.modify('
insert @xUpdate
after (/person/personal_info)[1]')

still fails.

Plus even with the example that works, I can't add more than one
<prof_qual></prof_qual> in the insert - every new node needs a
separate insert statement.

Ultimately, what I want to do is build very complex xml structures in
clean way, e.g. get all the occurances of <prof_qual> into the
variable and then just insert the variable.

[Kent Tegels]

Hello BradR,

The sql:variable() function is what you'd need to resolve @xUpdate in your
query (remember that @xUpdate is an attribute in XQuery). That said, if you
try this:

set @xPerson.modify('insert sql:variable("@xUpdate") into /person[1]')

You'll be sadly disappointed. You can't use the sql:variable function to
inject XML into XML. And converting xUpdate to nvarchar(...) isn't going
to help you either for less obvious reasons.

IMHO don't use XMLDML for this, its just too much of a PITA. You're better
off you write a SQLCLR function that does the insert for you.

Your milage may vary.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

[BradR]

Post by Kent Tegels
Hello BradR,
You'll be sadly disappointed. You can't use the sql:variable function to
inject XML into XML. And converting xUpdate to nvarchar(...) isn't going
to help you either for less obvious reasons.
IMHO don't use XMLDML for this, its just too much of a PITA. You're better
off you write a SQLCLR function that does the insert for you.
Your milage may vary.
Thanks!
Kent Tegels
DevelopMentorhttp://staff.develop.com/ktegels/

I've been investigating this for a while. It looks like it's possible
to solve this problem with FOR XML EXPLICIT, but that looks really
messy. Can you point me to a useful SQLCLR example?

Thanks!

[Michael Rys [MSFT]]

Instead of the EXPLICIT mode, look at the PATH mode in FOR XML.

Best regards
Michael

Post by BradR

Post by Kent Tegels
Hello BradR,
You'll be sadly disappointed. You can't use the sql:variable function to
inject XML into XML. And converting xUpdate to nvarchar(...) isn't going
to help you either for less obvious reasons.
IMHO don't use XMLDML for this, its just too much of a PITA. You're better
off you write a SQLCLR function that does the insert for you.
Your milage may vary.
Thanks!
Kent Tegels
DevelopMentorhttp://staff.develop.com/ktegels/

I've been investigating this for a while. It looks like it's possible
to solve this problem with FOR XML EXPLICIT, but that looks really
messy. Can you point me to a useful SQLCLR example?
Thanks!