Not getting all data using FOR XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Not getting all data using FOR XML

Author

Topic

bombdrop
Starting Member

5 Posts

Posted - 2006-03-03 : 05:38:40

Hi all just started to try out the XML fetures of SQL Server 2000
Below is my first go at producing an xml result set.


SELECT   * from stores

FOR XML AUTO

The problem is it does not return the full results as expected.
below is what i get

quote:

XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<stores stor_id="6380" stor_name="Eric the Read Books" stor_address="788 Catamaugus Ave." city="Seattle" state="WA" zip="98056"/><stores stor_id="7066" stor_name="Barnum's" stor_address="567 Pasadena Ave." city="Tustin" state="CA" zip="92789"/><stores

(6 row(s) affected)

as you can see it says it has returned 6 rows ( this is the amount in the table) but there is not 6 rows of information produced in the XML

any help would be great.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-03 : 06:08:26

if this is Query Analyzer then set you returned text length to 8000 in options.

Go with the flow & have fun! Else fight the flow

bombdrop
Starting Member

5 Posts

Posted - 2006-03-03 : 06:13:16

Thanks alot ( feel a bit of boob now)

Kristen
Test

22859 Posts

Posted - 2006-03-03 : 06:23:26

It will still wordwrap, or cut off, at 8,000 characters. There is no simple way (that I know of

) to get XML out of SQL without restoring to an application which uses a Stream to run the query, or using BCP (but that produces a raft of warning messages, which are not actually relevant). If someone knows of something I'm all ears!

For only a handful of row Query Analyser (configured to 8,000 column width) may well be fine.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources