FOR XML AUTO returns incomplete xml

I have two SQL tables that are populated based on data in SQL system tables.
When I run the FOR XML AUTO select statement on these tables, certain fields
will be missing end tags AND the data will not all be returned. In some
cases the end tags are there but the data is incomplete. When running the
query against the actual system table I'll get similar results but not
exactly the same. Any suggestions?
Using sql 2000 sp3. SQLXML 3 sp2
--master..sysaltfiles table data. Return only a few db's and an incomplete
select rtrim(filename) filename from mridiag..tbldbfiles for xml auto
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Data.MDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Log.LDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\master.mdf
NT\data\pubs_log.ldf
(24 row(s) affected)
--master..sysprocesses table. Only returns 1 incomplete record
select LASTWAITTYPE test from master..sysprocesses for xml auto
<master..sysprocesses test="SLEEP
(15 row(s) affected)
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
>I have two SQL tables that are populated based on data in SQL system
>tables.
> When I run the FOR XML AUTO select statement on these tables, certain
> fields
> will be missing end tags AND the data will not all be returned. In some
> cases the end tags are there but the data is incomplete. When running the
> query against the actual system table I'll get similar results but not
> exactly the same. Any suggestions?
What client are you using to retrieve the results?
You might also check this FAQ:
http://sqlxml.org/faqs.aspx?faq=76
Bryant
|||Thanks for the response.
I reviewed the link to the FAQ and compared it to how I'm doing it. First,
the information I posted was using Query Analyzer but I get the same results
when executing it from my vb script.
I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
link I based my code on is below. It's basically the same as the "VB
Example" on the faq you pointed me to but the version of XML on the FAQ is
3.0 and the version used in my script is 4.0. Other than that, I can't see
any differences.
Any more suggestions or questions? It really has me puzzled.
Thanks again.
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||Here is the link I mentioned.
http://www.sqlxml.org/faqs.aspx?faq=10
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||The query analyzer is using ODBC and not the OLEDB stream object and thus
only get junked XML back. Also, unless you increase the number of bytes
displayed per line, it does drop information.
If you are using the SQLOLEDB stream interface, you should get the XML back.
Can you try it with the SQLXML HTTP component to see if the XML is correctly
generated by the FOR XML query?
Thanks
Michael
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:9679959F-BC15-48CC-B4F9-7B521D883DCC@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> I reviewed the link to the FAQ and compared it to how I'm doing it.
> First,
> the information I posted was using Query Analyzer but I get the same
> results
> when executing it from my vb script.
> I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
> link I based my code on is below. It's basically the same as the "VB
> Example" on the faq you pointed me to but the version of XML on the FAQ is
> 3.0 and the version used in my script is 4.0. Other than that, I can't
> see
> any differences.
> Any more suggestions or questions? It really has me puzzled.
> Thanks again.
> "Bryant Likes" wrote: