exporting xml

[gacaccia]

hi all,

i've been experimenting with the "for xml auto" addition to select statements to get xml data. one problem i'm having is the formating of the output. it's all exactly what i want as far as the tags and attributes are concerned, but whereas the ms examples show output that is formatted nicely with child elements showing indented on separate lines and whatnot, my results are coming out on a single line that get's cut off after so many characters. is there something i'm missing for getting a more user friendly output?

as an example, i want to have...

<parent element attribute=&quot;value&quot;>
    <child element/>
</parent>

what i'm getting from sql server is...

<parent element attribute=&quot;value&quot;><child element/></par

thanks,

glenn

 

[NoCoolHandle]

The problme is you are looking at the output in QA..

There is an option that lets you set the length of the return from a query. The default max column output is 256 char.. Not near long enough for an XML output ...

Go to Tools -> Options -> Results and look for max col

set this to something like 8000 (the max) and try to write a query that is smaller than the 8000 bytes you just specified..
Otherwise you will need to do this from some other client..

OSQL to an output file is not a bad idea.. save it with an XML extension and view the results in IE

Have fun

Rob

 

[gacaccia]

rob,

thanks for the response. i had looked at the option for increasing max col width, but i'm concerned that for some queries i'll exceed the max specified. i checked out ms' web site on osql and got some good info there. one additional q. i see a switch of /o for sending output to a file, but this sends all the output to a file, including commands that you enter. is there a way to just send the results of a query to file when using osql?

thanks,

glenn

 

[nidgep]

HI gacaccia

If you want the output to look heirarchical then look into changing the auto parameter. If I remember correctly you can use the 'raw' attribute to get the output to indent with the plus [+] and minus [-] appearing next to each parent when viewing the results in IE5 or greater.

Hope this is what you meant.