SQLXML : How To Save XML Query Results to a File Using BCP

  • Written by belle
  • Posted March 21, 2009 at 6:20 am

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.

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.3/10 (28 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 5 votes)
12 Comments
Filed under: musings, SQLXML, T-SQL Tips and Tricks

SQLXML : How to Bulk Load XML From a File Using OPENXML

  • Written by belle
  • Posted March 20, 2009 at 12:45 am

Using OPENXML you can import XML documents into an XML variable, or into a table that has XML columns. The following is an example:

   1: -- ==========================================================================
   2: -- Object       : OPENXML1.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Developer    : Donabel Santos
   6: -- Origin       : 2008/08/17
   7: -- Last Modified: 2008/10/04
   8: -- Notes        : 
   9: -- ==========================================================================
  10:  
  11: -- bulk load
  12: -- For this example, XML file must be saved in C:
  13: -- XML file also should specify UTF-8 encoding, ie:
  14: -- <?xml version="1.0" encoding="UTF-8"?>
  15:  
  16: DECLARE @XMLTable TABLE
  17: (
  18:    xmlcol XML
  19: )
  20:  
  21: INSERT INTO @XMLTable(xmlcol)
  22: SELECT
  23:     InvoicesXML
  24: FROM
  25: (
  26:     SELECT * 
  27:     FROM OPENROWSET(BULK 'c:invoice.xml',SINGLE_BLOB) AS Invoices
  28: ) AS Invoices(InvoicesXML)
  29:  
  30: SELECT *
  31: FROM @XMLTable
  32:  
  33: 

VN:F [1.9.22_1171]
Rating: 9.1/10 (12 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)
1 Comment
Filed under: DBA Toolbox / T-SQL Scripts, SQLXML, T-SQL Tips and Tricks
`