FOR XML EXPLICIT - Gaps in result set

I have a query that returns four levels of nested data. The problem is that in the data is missing in sporadic places in the result set. Is there a size limitation in the result set?
SQL Server is returning what looks to be multiple result sets to the client. The "gaps" in data seem to coincide with the end of one result set and the start of the next result set.
Is there an option I can set to return one result set?
Any help with this would be greatly appreciated.
Here is my query:
/************************************************** ************
Return the results as an XML string.
************************************************** *************/
Exec ('Select 1 as Tag,
Null as Parent,
t.[Name] As [GrandParent!1!Name!xml],
Null As [Parents!2!Name!xml],
Null As [Children!3!Name!xml],
Null As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t
Union All
Select 2 As Tag,
1 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
Null As [Children!3!Name!xml],
Null As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p
Where t.MyID = p.ParentID
Union All
Select 3 As Tag,
2 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
c.[Name] As [Children!3!Name!xml],
c.[Description] As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p, Children c
Union All
Select 4 As Tag,
3 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
c.[Name] As [Children!3!Name!xml],
c.[Description] As [Children!3!Description!xml],
g.[Name] As [GrandChildren!4!Name!xml],
g.[Description] As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p, Children c, GrandChildren g
Order By [GrandParent!1!Name!xml], [Parents!2!Name!xml], [Children!3!Name!xml], [GrandChildren!4!Name!xml]
For XML Explicit')
A sample of the results follows (Unfortunately my formating did not come thru in the post so I manually indented. My comments are preceeded by ***):
<GrandParent><Name>Investment Practice</Name><Parents><Name>AAA - Asset Allocation Analysis/Strategy</Name><Children><Name>Industries Followed</Name><Description>Industries Followed</Description><GrandChildren><Name>CNS1 - Asset Allocation Strategy</Name>
***This is a different GrandChild Node The data between The start of the previous Grandchildren node(CNS1) and the "
n-cyclical Consumer Goods</Name> is gone. ***
<
n-cyclical Consumer Goods</Name><Description>EAI5</Description></GrandChildren><GrandChildren><Name>EAI6 - Health Care / Non-cyclical Services</Name><Description>EAI6</Description></GrandChildren><GrandChildren><Name>EAI7 - Financials</Name><Description>E
A
***This is a different GrandChild Node The data between The start of the previous Grandchildren node(EAI7) and the "me> is gone. ***
me>FIS1 - Treasuries/Sovereign/Agencies/TIPS</Name><Description>FIS1</Description></GrandChildren>
***This is what a GrandChild node should look like.
<GrandChildren><Name>FIS2 - Corporate - Investment Grader</Name><Description>FIS2</Description></GrandChildren><GrandChildren><Name>FIS3 - Mortgage Backed/XXX Portfolio</Name><Description>RMS4</Description></GrandChildren>
Further investigation reveals that the result is being broken in to 256 character result sets with gaps in the data between result sets.
Is there a conguration setting to increase the size? I haven't been able to find anything yet.
Can anyone help?
|||It finally dawned on me to check the options in Query Analyzer and I was able to change the default column width to the maximum of 8192.
This looks better. I am still getting multiple result sets, but I don't see any gaps (yet).
|||QA uses ODBC which is not supporting the FOR XML stream output well. You
should use ADO, OLEDB or ADO.net in order to programmatically retrieve FOR
XML results from the database.
Best regards
Michael
"Casey Loranger" <anonymous@.discussions.microsoft.com> wrote in message
news:FC5B9E3D-1177-4576-837E-C2C761FF0919@.microsoft.com...
> It finally dawned on me to check the options in Query Analyzer and I was
> able to change the default column width to the maximum of 8192.
> This looks better. I am still getting multiple result sets, but I don't
> see any gaps (yet).
>