One way to save your XML query results to the file system is by using bcp (bulk copy program).
Be aware of the following before deciding to use bcp for your regular export requirements:
- bcp is a program external to SSMS. If you need to use this from within your scripts, you will need to enable xp_cmdshell. xp_cmdshell is an extended stored procedure that allows external command line processes to be executed from within SQL Server. Enabling xp_cmdshell is considered to be a big no no in terms of security because this opens up avenues for malicious attacks through SQL Server.
- Depending on how much data you need to export, you may need to batch your export to overcome rowsize limitations of bcp.
If you intend to use bcp from within SSMS, you will need to enable xp_cmdshell first, otherwise you will get the following error:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.
The following example walks you through enabling xp_cmdshell, and using bcp from within SSMS to save your XML query to an external file.
1: USE AdventureWorks
2: GO
3:
4: -- --------------------------------------------------------
5: -- Enable xp_cmdshell
6: -- --------------------------------------------------------
7:
8: -- Allow advanced options to be changed.
9: EXEC sp_configure 'show advanced options', 1
10: GO
11:
12: -- Update the currently configured value for advanced options.
13: RECONFIGURE
14: GO
15:
16: -- Enable xp_cmdshell
17: EXEC sp_configure 'xp_cmdshell', 1
18: GO
19:
20: -- Update the currently configured value for xp_cmdshell
21: RECONFIGURE
22: GO
23:
24: -- Disallow further advanced options to be changed.
25: EXEC sp_configure 'show advanced options', 0
26: GO
27:
28: -- Update the currently configured value for advanced options.
29: RECONFIGURE
30: GO
31:
32:
33: -- --------------------------------------------------------
34: -- Sample export via bcp
35: -- --------------------------------------------------------
36: DECLARE @FileName VARCHAR(50)
37: DECLARE @SQLCmd VARCHAR(500)
38:
39: SELECT @FileName = 'C:TempSampleXMLOutput.xml'
40:
41: -- in this command, we are making sure there is only one ROOT node
42: SELECT @SQLCmd = 'bcp ' +
43: '"SELECT Resume ' +
44: ' FROM AdventureWorks.HumanResources.JobCandidate ' +
45: ' FOR XML PATH(''''), ROOT(''HRResumes''), TYPE "' +
46: ' queryout ' +
47: @FileName +
48: ' -w -T -S' + @@SERVERNAME
49:
50: -- display command, for visual check
51: SELECT @SQLCmd AS 'Command to execute'
52:
53: -- create the XML file
54: EXECUTE master..xp_cmdshell @SQLCmd
55:
56:
- Tweet
Thank you! It is very useful (at least to me).
Less is more. Besides being short, sweet, and very elegant I use the little script in processing my XML almost every day.
Keep it up Belle!
Thanks John!
Happy to hear that!
Thanks, it’s useful and worked for me.
Just a note:
* the -S option is required only in case of remote execution of bpc. I had to cut it.
* VARCHAR(500) may be too much limiting
I want to add a very important note. With bigger result sets I got a line brakes (and consequently the stored xml corrupted). The only thing that solved the problem was to add the options -r -t without argument. This instructs bps to override newline (default n) and tab (default t) with a null character (that is your final xml will not have line brakes or tabs but will be well formed!!!).
Furthermore, you can use batch to fix the limit size. Here the final instruction fragment for the bcp I used:
SELECT @SQLCmd = 'bcp ... queryout ... -w -r -t -b 4000'
This is very cool … thanks so much for posting this.
I got error:
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user ‘NT AUTHORITYSYSTEM’.
I checked the DB , it has user ‘NT AUTHORITYSYSTEM’
Thanks, Belle! I had everything going right, could read the XML in SSMS but not a browser. Needed the little word ‘TYPE’. Your example saved my day, but I still need to go back and learn what in the world ‘TYPE’ had to do with my XML failing.
The output from mine returned the following and did not create the file, as expected.
NULL
Starting copy…
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)
NULL
The BCP line reads as follows:
bcp “SELECT * FROM mytable FOR XML PATH(”), ROOT(‘DATA’), TYPE ” queryout C:datatest1.xml -w -T -Smyserver
I executed the query part which produced valid XML
SELECT * FROM mytable FOR XML PATH(”), ROOT(‘DATA’), TYPE
file is not saving in .xml format
i am getting record in this way.
38312/05/11638412/05/11738512/05/11838612/05/11938712/05/12038812/05/12138912/05/12239012/05/12339112/05/12439212/05/12539312/05/12639412/05/12739512/05/12839612/05/12939712/05/13039812/05/13139912/05/13240012/06/00140112/06/00240212/06/00340312/06/00440412/06/00540512/06/00640612/06/00740712/06/00840812/06/00940912/06/01041012/06/01141112/06/01241212/06/01341312/07/00141412/07/00241512/07/00341612/07/00441712/08/001
Many thanks! I’ve been beating my head against a stone
wall for three days, but this post just solved my problems.
getting below error while executing
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
any one know this error?