Query output to XML

Question

Query output to XML

Aardvark-878532

SSC Enthusiast

Points: 159
More actions
September 12, 2008 at 6:08 am

#77086

Hi There, I'm a XML newbie and I'm stuck with a query that I would like to take data from the database and present it into an XML file.
For example the query would be select column1, column2 from customers. I would like to be able to call it from a batch file in bcp.
As I newbie I'm really stuck with this and would appreciate some assistance.
Many thanks,
Nichola.

Viewing 8 posts - 1 through 7 (of 7 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Since I have no idea in which format you want the XML output I can only advise to read up on the "FOR XML" option of a select statement.

The basic syntax would be

SELECT col1, col2 from myTable For XML AUTO

See also the Workshop here:

http://www.sqlservercentral.com/articles/SS2K5+-+XML/3059/

[font="Verdana"]Markus Bohse[/font]

Aardvark-878532 SSC Enthusiast Points: 159 More actions · Answer 2

Hi,

Thanks for your reply.. Here's where I'm stuck at, I have the query below in place.

select ' ';

SELECT

DATE,

NAME,

sum(TOTAL),

FROM TABLE1

WHERE (DATE > @date1 AND DATE < @date2 )

GROUP BY DATE, NAME

ORDER BY NAME,

for xml auto;

select ' '

I need to be able to get this out to a file, however I cannot used the group by in the for sum(total).

Any help would be most appreciated.

Thanks.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

Not sure if I understand your problem.

Apart from the comma before the "For XML" your query looks good to me.

This should work (can't test it right now), at least if you have a column which is called Total.

SELECT

DATE,

NAME,

sum(TOTAL),

FROM TABLE1

WHERE (DATE > @date1 AND DATE < @date2 )

GROUP BY DATE, NAME

ORDER BY NAME

for xml auto;

[font="Verdana"]Markus Bohse[/font]

Aardvark-878532 SSC Enthusiast Points: 159 More actions · Answer 4

Hi,

Thanks for your last reply. Apologies for the delay in replying I have been attempting to complete the query myself, unfortunately without any luck.

The message that is displayed is "GROUP BY and aggregate functions are currently not supported with FOR XML AUTO."

Thanks.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Wrap your query into derived table and select FOR XML from there.

_____________
Code for TallyGenerator

Aardvark-878532 SSC Enthusiast Points: 159 More actions · Answer 6

Aardvark-878532

SSC Enthusiast

Points: 159

September 17, 2008 at 9:08 am

#873067

My issue is now resolved, Many thanks.

Sandip-818572 SSC Enthusiast Points: 130 More actions · Answer 7

Is it possible to save xml output of query, directly to file ?? If anyone knows please help.

=======================================
[font="Tahoma"]"Dont let someone be priority in your life if you are simply option for someone."[/font]