Post reply

How can I save the results of a query that produces XML to a file?

  • June 20, 2011 at 7:07 am

    #238195

    How can I save the results of a query that produces XML to a file? Currently I just select @myXML then I open it in the results window and do a file save as. I have seen articles referencing xp_Cmdshell, but that is not available to me.

    Thank you!

  • June 20, 2011 at 7:38 am

    #1341193

    There are differant ways to do this depending on your version of SQL server.

    I will give you one that works with SQL 2005 and above.

    This is something that MSDN.microsoft.com search can help you find also. Here is a nice article covering how to use CLR to write an assymbly to do it.

    http://forums.asp.net/p/1133561/1806213.aspx

    Here is another one. from Google Search

    http://www.sqldbatips.com/showarticle.asp?ID=23

    A lot of persons learning CLR learn this as thier first CLR program.

    Kind of a SQL CLR version of Hello World. It is even in the M$ SQL 2005 training materials.

    If you are on SQL 2000 you should use an external program or script and connect to the SQL server using OSQL, then execute your SQL get the XML output and save it to a file. There are other ways, but they can be difficult to maintain.

    Have Fun...:cool:

  • June 20, 2011 at 8:09 am

    #1341213

    Use BCP.exe or create an SSIS package.

  • June 20, 2011 at 8:53 am

    #1341248

    Oliiii (6/20/2011)

    Use BCP.exe or create an SSIS package.

    You would need the Enterprise or Datacenter version of SQL server before you could start wrting solutions with BCP or SSIS. I was not able to find a link to any example of saving XML in a query directly to file instead of result set for either of these options.

  • June 20, 2011 at 9:38 am

    #1341276

    You do not need Enterprise or DataCenter to use bcp or SSIS. These are available in standard edition as well. Is the goal to script this so that it writes to file every time, or will this only be done from SSMS? If you are doing it in SSMS, you can actually change the option to write results to file.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • June 20, 2011 at 9:55 am

    #1341290

    jared-709193 (6/20/2011)

    You do not need Enterprise or DataCenter to use bcp or SSIS. These are available in standard edition as well. Is the goal to script this so that it writes to file every time, or will this only be done from SSMS? If you are doing it in SSMS, you can actually change the option to write results to file.

    Thanks,

    Jared

    I guess you could use the script object and some VB .Net code instead of the SSIS file controls and use standard edition. Just really can't think of a good set of example code to post a link to that could show anyone how to do that.

    I have never heard of using BCP.exe to make an XML file and can not find an example for that either.

    It has been done a lot with "sqlcmd" the command line SQL utility.

    http://msdn.microsoft.com/en-us/library/ms162773.aspx

    Should be very easy to automate like that.

    How about you guys post some links with your suggestions?

  • June 20, 2011 at 10:00 am

    #1341295

    How about you guys post some links with your suggestions?

    Check out this link: http://www.sqlservercentral.com/Forums/Topic291798-148-1.aspx

    Jared
    CE - Microsoft

  • June 20, 2011 at 10:20 am

    #1341308

    Here is a bcp.exe example:

    bcp "SELECT * FROM master.sys.tables FOR XML AUTO, TYPE" queryout C:\example.xml -S SERVER\INSTANCE -T -c

    bcp can be used against any version of SQL Server.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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