Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Attributes and elements in FOR XML subqueries

9 views
Skip to first unread message

Chloe C

unread,
Dec 29, 2009, 6:50:53 AM12/29/09
to
Hi

I'm trying to build an XML document from a relational schema. A lot of
it works well, but I've having trouble building sub-elements which
have attributes as well as data. A simplified example is:

Tables

Name NameId Type
1 person

NamePart NameId Type Value
1 given Chloe
1 family Crowder

What I want is
<name type="person"><namePart type="given">Chloe</namePart><namePart
type="family">Crowder</namePart></name>

I've tried
SELECT n.type as "@type", (SELECT np.type AS "@type", np.value as
"namePart" FROM NamePart np WHERE np.NameId = n.NameId FOR XML
PATH(N'namePart'), TYPE)
FROM Name n
FOR XML PATH(N'name'),
TYPE


But this gives an unwanted extra layer of <namePart></namePart> (with
the attribute on the outer one). Omitting namePart from the SELECT
clause gives an unwanted <value></value> round np.value, while
omitting namePart from the FOR XML Path clause gives a compilation
error

Msg 6864, Level 16, State 1, Line 1
Row tag omission (empty row tag name) cannot be used with
attribute-centric FOR XML serialization.

Any bright ideas on this?

Chloe

Martin Honnen

unread,
Dec 29, 2009, 8:52:50 AM12/29/09
to
Chloe C wrote:
> Hi
>
> I'm trying to build an XML document from a relational schema. A lot of
> it works well, but I've having trouble building sub-elements which
> have attributes as well as data. A simplified example is:
>
> Tables
>
> Name NameId Type
> 1 person
>
> NamePart NameId Type Value
> 1 given Chloe
> 1 family Crowder
>
> What I want is
> <name type="person"><namePart type="given">Chloe</namePart><namePart
> type="family">Crowder</namePart></name>

SELECT


n.type as "@type",
(SELECT
np.type AS "@type",

np.value as "text()"


FROM NamePart np WHERE np.NameId = n.NameId
FOR XML PATH(N'namePart'), TYPE)
FROM Name n
FOR XML PATH(N'name'), TYPE

--

Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

Chloe C

unread,
Dec 29, 2009, 9:08:08 AM12/29/09
to
Martin - perfect! It's easy when you know how (and find the right
place in the documentation).

Many thanks.

On Tue, 29 Dec 2009 14:52:50 +0100, Martin Honnen <maho...@yahoo.de>
wrote:

0 new messages