Generated KML file from SQL query save to local drive

Question

My SQL Query generates a XML output:

         select 'TEST.kml' as name,
                 (select 'TEST' as name, (
                 select ( 
                       select top 10 issue as name,
                         null as description,
                         null as 'Point/coordinates',
                         (
                              select 
                                        null as altitudeMode,
                                        Coordinates as 'coordinates'
                              for xml path('Polygon'), type)
                 from Mapping for xml path('Placemark'), type))
                     for xml path ('Line') , type)
                 for xml path ('Doc'), root('kml'))

I want to save the output of the query as .XML file on to local drive.Please advise.

Duplicate of this question: [Generating XML file from SQL Server 2008](https://stackoverflow.com/q/1803911/243373). — TT., Oct 03 '17 at 05:33
It's missing some context. What trigger this query? A web app which outputs to the user a download? A scheduled job which stores a single queried parameter output to the FS? Or a schedeled job that traverses a series of parameters and for each one grasps an output file? Or other that traverses a series of parameters generating a single file? — Marcus Vinicius Pompeu, Oct 05 '17 at 06:14

dbajtr · Answer 1 · 2017-10-04T12:43:28.113

Not the most elegant way but it is possible to use bulk copy program and xp_cmdshell to do this. Few things first, xp_cmdshell is blocked by default by SQL Server as part of the security configuration so you will need to enable that first and BCP requires you to have access to the directory that you want to create the file.

To enable xp_cmdshell you'll need run sp_configure and RECONFIGURE, use this:

EXEC sp_configure'xp_cmdshell', 1
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

Then you can run the following:

EXEC xp_cmdshell 'bcp "SELECT * FROM [Database].dbo.[Table] FOR XML AUTO,
ELEMENTS" queryout "C:\test.xml" -c -T'

Just add your query into it and make sure you add [] around your table names.

The Microsoft Documents for xp_cmdshell are here and bcp can be found here

score 3 · Answer 2 · answered Oct 04 '17 at 08:29

Using bcp is definite choice especially when working with large data sets. Alternatively, you can try using SQL Management Studio - Export Data.

Open the interface - Right Click on database name, then Tasks, then Export Data
The menu is opened. Click Next
Then choose SQL Server Native Client, sql server, database name and authentication method:
Then where to save the data:
Then how we are getting the data (in your case SQL query):
Past the query:
Then we have some settings, click finish.

Dan Guzman · Answer 3 · 2017-10-10T15:23:49.537

To save the results of a remote query to a local file, you could use a Powershell script like this example:

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI")
$command = New-Object System.Data.SqlClient.SqlCommand(@("
    select 'TEST.kml' as name,
                 (select 'TEST' as name, (
                 select ( 
                       select top 10 issue as name,
                         null as description,
                         null as 'Point/coordinates',
                         (
                              select 
                                        null as altitudeMode,
                                        Coordinates as 'coordinates'
                              for xml path('Polygon'), type)
                 from Mapping for xml path('Placemark'), type))
                     for xml path ('Line') , type)
                 for xml path ('Doc'), root('kml');"), $connection);
$connection.Open();
$command.ExecuteScalar() | Out-File -FilePath "C:\KmlFiles\YourFile.kml";
$connection.Close();

The script can be executed from a command prompt by saving the script to a file with a ".ps1" extension and using a command like:

powershell -ExecutionPolicy RemoteSigned -File "C:\PowershellScripts\ExampleExport.ps1"

This command can be scheduled using a Windows Task Scheduler task to automate the export. Alternatively, schedule using a SQL Server agent job with a Powershell or CmdExec step.

hi how do we execute this script? – user1046415 Oct 10 '17 at 13:45 — user1046415, Oct 10 '17 at 13:45
@user1046415, I added methods to my answer. – Dan Guzman Oct 10 '17 at 15:24 — Dan Guzman, Oct 10 '17 at 15:24

Generated KML file from SQL query save to local drive

3 Answers3