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

Chloe C wrote:
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/

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 <mahotrash (AT) yahoo (DOT) de>
wrote: