Problem retrieving XML from Sql server.

Question

Post reply

Problem retrieving XML from Sql server.

zahi antoine

SSC Journeyman

Points: 97
More actions
June 20, 2005 at 4:54 am

#86496

Hi everybody, i am new on this website and i am a new user of SQL server 2000.
Actually , i created a small table called books okay and i want to retrieve an XML file from this table. Once, it works i will be able to use the code to retrieve xml info from databases generated from automates.
I understood from the sql tutorials how the FOR clauses stuff works but the problem is that the result is really bizzar...it is like somebody stopped the for loop in the middle...here what i have for a result :
"<edition number="1     ">Scie </edition>
<edition number="2     ">Math </edition>
<edition number="3     ">Litt </edition>
<edition number="4     ">Litt </edition>
<edition number="5     ">Cult </edition>
<edition number="6     ">Roma </edition>
<edition n                                           "
knowing that i have like 50 number and i get just 6, obviously i have a problem...
Please can you help on this ? i will really appreciate it.
P.S.: sorry for my weak english, it is not my native language.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This works for me : Select * from dbo.SysObjects for XML auto

Hans Lindgren SSChampion Points: 10494 More actions · Answer 2

Hmm. Are you executing this query in query analyzer? Are you aware of the default 256 char column limit in that application?

Try to direct your query into a file and see if that helps...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Just to clarify :

Tools / Options / Results / Max Charaters per column, mine is set to 2000.

Btw I was using QA to test my results.

Hans Lindgren SSChampion Points: 10494 More actions · Answer 4

Haha

Juste to clarify Remi Gregoire, I was directing my answer to zahi antoine. Have you seen that his query output is EXCACTLY 256 chars?

//'appy swimming, Hanslindgren

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Each line seems to have between 35 and 40 characthers. (6*40 = 240). So I guess that it would be the most likely answer, but untill we hear from him again, I'm gonna add another one.

If you remove the for xml clause, how many rows do you get?

Hans Lindgren SSChampion Points: 10494 More actions · Answer 6

True, but taking the string and removing the LineFeeds (which usually are not outputted in XML) you have:

<edition number="1 ">Scie </edition><edition number="2 ">Math </edition><edition number="3 ">Litt </edition><edition number="4 ">Litt </edition><edition number="5 ">Cult </edition><edition number="6 ">Roma </edition><edition n

Which is 256 chars...

zahi antoine SSC Journeyman Points: 97 More actions · Answer 7

Hi again,

thank you Rémi grégoire and Hanslindgren, it is really kind.

Indeed, i had just 256 char so i changed it to 2000 and now it is working perfectly...i feel ashame i didn't change it before.

But i have another question, my automata generates a huge database so when i will transform it to XML file it is gonna be really big so i will be obliged to change the 2000 to whatever , right? ...don't you think that there's an option that takes the adequate lenght automatically ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

If you use the openxml functions in vb, you'll be fine. The 2000 limit is for display only and in QA only.

Sanjay Pandey-195693 Valued Member Points: 68 More actions · Answer 9

Hi guys,

How do I direct the xml output to a file. What I want to do is to retrieve the xml through a query like,

select .... for xml auto

and then to launch it into browser using xsl files.

Anyway I can do it ???

zahi antoine SSC Journeyman Points: 97 More actions · Answer 10

Hi Sanjay,

To export the query result into a file , it is simple you just have to open the Query Analyser to right click under it and then to choose "results to file".

To open it in browser using XSL , you have to insert in the XML file a line referring to your XSL file.

I tested it and it works very fine.

good luck.

Hans Lindgren SSChampion Points: 10494 More actions · Answer 11

zahi antoine,

have you tried it with alot of XML? Have you seen that it will be malformed?

As stated before. QA has a ROW LENGHT LIMIT.

Sanjay Pandey-195693 Valued Member Points: 68 More actions · Answer 12

Thanks Zahi and Hanslindgren,

I can do by using the IE and giving the command as URL and then by saving the file.

But my problem is that i want to do it programmatically. I am using ASP technology on my web page on IIS. The codes are written using VBSCript.

Hans Lindgren SSChampion Points: 10494 More actions · Answer 13

I guess there should be alot of information on the web. Have you tried to google for OPENXML and VBSCRIPT?

//Hanslindgren

zahi antoine SSC Journeyman Points: 97 More actions · Answer 14

Hanslindgren,

As you said, QA has a row lenght limit. But when you use IE and you retrieve XML from SQL through http, it works fine even if the file is big.it is logic since you don't use QA to show the result!

Sanjay,

Actually i would like to do the same thing, automatically save the file further to retrieving it from the URL.

Afterall, to save the file manually takes 2 seconds , so...

Anyway, we'll keep in touch.