Post reply

Query output to XML

  • 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.

  • September 12, 2008 at 6:19 am

    #870891

    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://qa.sqlservercentral.com/articles/SS2K5+-+XML/3059/

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

  • September 12, 2008 at 7:23 am

    #870933

    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.

  • September 12, 2008 at 9:29 am

    #871084

    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]

  • September 16, 2008 at 7:47 am

    #872373

    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.

  • September 16, 2008 at 2:14 pm

    #872696

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

    _____________
    Code for TallyGenerator

  • September 17, 2008 at 9:08 am

    #873067

    My issue is now resolved, Many thanks.

  • September 23, 2008 at 5:47 am

    #875570

    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]

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

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