FOR XML AUTO not producing the proper parent-child hierarchy

I have a query that uses two views that should produce a simple parent-child XML document however it repeats every
parent (topic) record for each child record. Here's the query:
select topic.id,
topic.name,
topic.sename,
topic.description,
topic.parentsubjectname,
topic.parentcategroyname,
0 WL,
item.id,
item.name,
item.sename,
item.about,
item.avgrating
from dbo.pico_topic_info topic
join dbo.pico_item_info item on topic.id = item.ParentTopicID
where topic.id = 344
order by topic.id, item.id
for xml auto, elements
I expect a document that looks like this:
<topic>
<id>344</id>
<name>Some Topic</name>
...
<item>
<id>123123</id>
<name>item 1 </name>
...
</item>
<item>
<id>543453</id>
<name>item 2</name>
...
</item>
</topic>
However I get a topic element with every item element:
<topic>
<id>344</id>
<name>Some Topic</name>
...
<item>
<id>123123</id>
<name>item 1 </name>
...
</item>
</topic>
<topic>
<id>344</id>
<name>Some Topic</name>
...
<item>
<id>543453</id>
<name>item 2</name>
...
</item>
</topic>
Any ideas why? I've not had this problem before.
--Buddy
Well, I figured it out. The description column is an ntext column which apparently doesn't play well with other columns
when the FOR XML clause is used.
--Buddy
Buddy Ackerman wrote:
> I have a query that uses two views that should produce a simple
> parent-child XML document however it repeats every parent (topic) record
> for each child record. Here's the query:
>
> select topic.id,
> topic.name,
> topic.sename,
> topic.description,
> topic.parentsubjectname,
> topic.parentcategroyname,
> 0 WL,
> item.id,
> item.name,
> item.sename,
> item.about,
> item.avgrating
> from dbo.pico_topic_info topic
> join dbo.pico_item_info item on topic.id = item.ParentTopicID
> where topic.id = 344
> order by topic.id, item.id
> for xml auto, elements
>
> I expect a document that looks like this:
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>123123</id>
> <name>item 1 </name>
> ...
> </item>
> <item>
> <id>543453</id>
> <name>item 2</name>
> ...
> </item>
> </topic>
> However I get a topic element with every item element:
>
>
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>123123</id>
> <name>item 1 </name>
> ...
> </item>
> </topic>
> <topic>
> <id>344</id>
> <name>Some Topic</name>
> ...
> <item>
> <id>543453</id>
> <name>item 2</name>
> ...
> </item>
> </topic>
>
> Any ideas why? I've not had this problem before.
>
> --Buddy