x

Output SQL data as XML and export to XML file

Hi,

Ive been able to translate my SQL data using TSQL and FOR XML PATH clause into XML format. Now I need to transport this XML result set into an XML file daily and upload to a FTP server.

Can I use SSIS to transport this XML result by running my query in a sproc daily to an XML file? How would I generate this XML file?

Thank you in advance.

Regards, Slick

more ▼

asked Apr 27, 2010 at 12:25 PM in Default

Slick84 gravatar image

Slick84
1.3k 75 102 142

Great answers to this question. Hope this helps others as well.
Apr 28, 2010 at 05:51 PM Slick84
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

You can use plain T-SQL to do this. There are many ways to save the result of the select query to file. For example, you can use xp_cmdshell if it is available. To check whether it is available or not you can try running the script below and if you get errors let me know so I can include the script to enable it.

Suppose you have a table and you want to select all its data in xml format where each column is a node rather than attribute. Here is the script to save the results to file:

declare @cmd nvarchar(255);

-- Please note that the fully qualified table name is needed
select @cmd = '
    bcp "select * from [your_db_name].[your_schema].[your_table] row for xml auto, root(''rows''), elements" ' +
    'queryout "e:\temp\sample.xml" -S YOUR_INSTANCE_NAME -T -w -r -t';

exec xp_cmdshell @cmd;
go

The switches are case sensitive. -T means use trusted connection. -S followed by the instance name is only needed if you connect to named rather than default instance. -w means that your data might contain nvarchars. -r -t means to strip the tabs,carriage returns and line feeds.

Hope this helps,

Oleg

more ▼

answered Apr 27, 2010 at 12:54 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Awesome...... Thanks!!!
Apr 27, 2010 at 01:09 PM Slick84
Even though this is an awesome answer, I voted for the other as the answer mainly because this requires xp_cmdshell which is sometimes disabled by the DBA due to security reasons.
Apr 28, 2010 at 05:51 PM Slick84
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Apr 27, 2010 at 01:07 PM

Bhaskar gravatar image

Bhaskar
333 16 17 20

Thank you so much. Good reading!
Apr 27, 2010 at 01:10 PM Slick84
This is a great article. I wish I could select this as the correct answer too. Great information and THANKS TO ALL for the contribution!
Apr 27, 2010 at 01:11 PM Slick84
(comments are locked)
10|1200 characters needed characters left

You can save an existing XML variable as a file from TSQL. Here is an example (I read it back in to compare)!

DECLARE @testXML XML 
SELECT @TestXML='<CATALOG><PLANT><COMMON>Bloodroot</COMMON><BOTANICAL>Sanguinaria canadensis</BOTANICAL><ZONE>4</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$2.44</PRICE><AVAILABILITY>031599</AVAILABILITY></PLANT><PLANT><COMMON>Columbine</COMMON><BOTANICAL>Aquilegia canadensis</BOTANICAL><ZONE>3</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$9.37</PRICE><AVAILABILITY>030699</AVAILABILITY></PLANT><PLANT><COMMON>Marsh Marigold</COMMON><BOTANICAL>Caltha palustris</BOTANICAL><ZONE>4</ZONE><LIGHT>Mostly Sunny</LIGHT><PRICE>$6.81</PRICE><AVAILABILITY>051799</AVAILABILITY></PLANT><PLANT><COMMON>Cowslip</COMMON><BOTANICAL>Caltha palustris</BOTANICAL><ZONE>4</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$9.90</PRICE><AVAILABILITY>030699</AVAILABILITY></PLANT><PLANT><COMMON>Dutchman''s-Breeches</COMMON><BOTANICAL>Dicentra cucullaria</BOTANICAL><ZONE>3</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$6.44</PRICE><AVAILABILITY>012099</AVAILABILITY></PLANT><PLANT><COMMON>Ginger, Wild</COMMON><BOTANICAL>Asarum canadense</BOTANICAL><ZONE>3</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$9.03</PRICE><AVAILABILITY>041899</AVAILABILITY></PLANT><PLANT><COMMON>Hepatica</COMMON><BOTANICAL>Hepatica americana</BOTANICAL><ZONE>4</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$4.45</PRICE><AVAILABILITY>012699</AVAILABILITY></PLANT><PLANT><COMMON>Liverleaf</COMMON><BOTANICAL>Hepatica americana</BOTANICAL><ZONE>4</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$3.99</PRICE><AVAILABILITY>010299</AVAILABILITY></PLANT><PLANT><COMMON>Jack-In-The-Pulpit</COMMON><BOTANICAL>Arisaema triphyllum</BOTANICAL><ZONE>4</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$3.23</PRICE><AVAILABILITY>020199</AVAILABILITY></PLANT><PLANT><COMMON>Mayapple</COMMON><BOTANICAL>Podophyllum peltatum</BOTANICAL><ZONE>3</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$2.98</PRICE><AVAILABILITY>060599</AVAILABILITY></PLANT><PLANT><COMMON>Phlox, Woodland</COMMON><BOTANICAL>Phlox divaricata</BOTANICAL><ZONE>3</ZONE><LIGHT>Sun or Shade</LIGHT><PRICE>$2.80</PRICE><AVAILABILITY>012299</AVAILABILITY></PLANT><PLANT><COMMON>Phlox, Blue</COMMON><BOTANICAL>Phlox divaricata</BOTANICAL><ZONE>3</ZONE><LIGHT>Sun or Shade</LIGHT><PRICE>$5.59</PRICE><AVAILABILITY>021699</AVAILABILITY></PLANT><PLANT><COMMON>Spring-Beauty</COMMON><BOTANICAL>Claytonia Virginica</BOTANICAL><ZONE>7</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$6.59</PRICE><AVAILABILITY>020199</AVAILABILITY></PLANT><PLANT><COMMON>Trillium</COMMON><BOTANICAL>Trillium grandiflorum</BOTANICAL><ZONE>5</ZONE><LIGHT>Sun or Shade</LIGHT><PRICE>$3.90</PRICE><AVAILABILITY>042999</AVAILABILITY></PLANT><PLANT><COMMON>Wake Robin</COMMON><BOTANICAL>Trillium grandiflorum</BOTANICAL><ZONE>5</ZONE><LIGHT>Sun or Shade</LIGHT><PRICE>$3.20</PRICE><AVAILABILITY>022199</AVAILABILITY></PLANT><PLANT><COMMON>Violet, Dog-Tooth</COMMON><BOTANICAL>Erythronium americanum</BOTANICAL><ZONE>4</ZONE><LIGHT>Shade</LIGHT><PRICE>$9.04</PRICE><AVAILABILITY>020199</AVAILABILITY></PLANT><PLANT><COMMON>Trout Lily</COMMON><BOTANICAL>Erythronium americanum</BOTANICAL><ZONE>4</ZONE><LIGHT>Shade</LIGHT><PRICE>$6.94</PRICE><AVAILABILITY>032499</AVAILABILITY></PLANT><PLANT><COMMON>Adder''s-Tongue</COMMON><BOTANICAL>Erythronium americanum</BOTANICAL><ZONE>4</ZONE><LIGHT>Shade</LIGHT><PRICE>$9.58</PRICE><AVAILABILITY>041399</AVAILABILITY></PLANT><PLANT><COMMON>Anemone</COMMON><BOTANICAL>Anemone blanda</BOTANICAL><ZONE>6</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$8.86</PRICE><AVAILABILITY>122698</AVAILABILITY></PLANT><PLANT><COMMON>Grecian Windflower</COMMON><BOTANICAL>Anemone blanda</BOTANICAL><ZONE>6</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$9.16</PRICE><AVAILABILITY>071099</AVAILABILITY></PLANT><PLANT><COMMON>Bee Balm</COMMON><BOTANICAL>Monarda didyma</BOTANICAL><ZONE>4</ZONE><LIGHT>Shade</LIGHT><PRICE>$4.59</PRICE><AVAILABILITY>050399</AVAILABILITY></PLANT><PLANT><COMMON>Bergamot</COMMON><BOTANICAL>Monarda didyma</BOTANICAL><ZONE>4</ZONE><LIGHT>Shade</LIGHT><PRICE>$7.16</PRICE><AVAILABILITY>042799</AVAILABILITY></PLANT><PLANT><COMMON>Black-Eyed Susan</COMMON><BOTANICAL>Rudbeckia hirta</BOTANICAL><ZONE>Annual</ZONE><LIGHT>Sunny</LIGHT><PRICE>$9.80</PRICE><AVAILABILITY>061899</AVAILABILITY></PLANT><PLANT><COMMON>Buttercup</COMMON><BOTANICAL>Ranunculus</BOTANICAL><ZONE>4</ZONE><LIGHT>Shade</LIGHT><PRICE>$2.57</PRICE><AVAILABILITY>061099</AVAILABILITY></PLANT><PLANT><COMMON>Crowfoot</COMMON><BOTANICAL>Ranunculus</BOTANICAL><ZONE>4</ZONE><LIGHT>Shade</LIGHT><PRICE>$9.34</PRICE><AVAILABILITY>040399</AVAILABILITY></PLANT><PLANT><COMMON>Butterfly Weed</COMMON><BOTANICAL>Asclepias tuberosa</BOTANICAL><ZONE>Annual</ZONE><LIGHT>Sunny</LIGHT><PRICE>$2.78</PRICE><AVAILABILITY>063099</AVAILABILITY></PLANT><PLANT><COMMON>Cinquefoil</COMMON><BOTANICAL>Potentilla</BOTANICAL><ZONE>Annual</ZONE><LIGHT>Shade</LIGHT><PRICE>$7.06</PRICE><AVAILABILITY>052599</AVAILABILITY></PLANT><PLANT><COMMON>Primrose</COMMON><BOTANICAL>Oenothera</BOTANICAL><ZONE>3 - 5</ZONE><LIGHT>Sunny</LIGHT><PRICE>$6.56</PRICE><AVAILABILITY>013099</AVAILABILITY></PLANT><PLANT><COMMON>Gentian</COMMON><BOTANICAL>Gentiana</BOTANICAL><ZONE>4</ZONE><LIGHT>Sun or Shade</LIGHT><PRICE>$7.81</PRICE><AVAILABILITY>051899</AVAILABILITY></PLANT><PLANT><COMMON>Blue Gentian</COMMON><BOTANICAL>Gentiana</BOTANICAL><ZONE>4</ZONE><LIGHT>Sun or Shade</LIGHT><PRICE>$8.56</PRICE><AVAILABILITY>050299</AVAILABILITY></PLANT><PLANT><COMMON>Jacob''s Ladder</COMMON><BOTANICAL>Polemonium caeruleum</BOTANICAL><ZONE>Annual</ZONE><LIGHT>Shade</LIGHT><PRICE>$9.26</PRICE><AVAILABILITY>022199</AVAILABILITY></PLANT><PLANT><COMMON>Greek Valerian</COMMON><BOTANICAL>Polemonium caeruleum</BOTANICAL><ZONE>Annual</ZONE><LIGHT>Shade</LIGHT><PRICE>$4.36</PRICE><AVAILABILITY>071499</AVAILABILITY></PLANT><PLANT><COMMON>California Poppy</COMMON><BOTANICAL>Eschscholzia californica</BOTANICAL><ZONE>Annual</ZONE><LIGHT>Sun</LIGHT><PRICE>$7.89</PRICE><AVAILABILITY>032799</AVAILABILITY></PLANT><PLANT><COMMON>Shooting Star</COMMON><BOTANICAL>Dodecatheon</BOTANICAL><ZONE>Annual</ZONE><LIGHT>Mostly Shady</LIGHT><PRICE>$8.60</PRICE><AVAILABILITY>051399</AVAILABILITY></PLANT><PLANT><COMMON>Snakeroot</COMMON><BOTANICAL>Cimicifuga</BOTANICAL><ZONE>Annual</ZONE><LIGHT>Shade</LIGHT><PRICE>$5.63</PRICE><AVAILABILITY>071199</AVAILABILITY></PLANT><PLANT><COMMON>Cardinal Flower</COMMON><BOTANICAL>Lobelia cardinalis</BOTANICAL><ZONE>2</ZONE><LIGHT>Shade</LIGHT><PRICE>$3.02</PRICE><AVAILABILITY>022299</AVAILABILITY></PLANT></CATALOG>'
-- We Store the contents of an XML variable to a table
CREATE TABLE MyXMLTable
    (
      xCol XML
    ) ;

INSERT  INTO MyXMLTable ( xCol )
        SELECT  @testXML

/*
   We Save an XML value to a file.
*/
DECLARE @Command VARCHAR(255)
DECLARE @Filename VARCHAR(100)

SELECT  @Filename = 'd:\files\TestXMLRoutine'
/* we then insert a row into the table from the XML variable */
/* so we can then write it out via BCP! */
SELECT  @Command = 'bcp "select xCol from ' + DB_NAME()
        + '..MyXMLTable" queryout ' 
       + @Filename + ' -w -T -S' + @@servername
EXECUTE master..xp_cmdshell @command
--so now the xml is written out to a file 
    SELECT CONVERT(nVARCHAR(max),BulkColumn)
FROM    OPENROWSET(BULK 'D:\files\TestXMLRoutine', SINGLE_BLOB) AS x 

--but we can turn this into a generic procedure
go

so this is the stored procedure

-----------------------------------------------------------------
IF OBJECT_ID (N'dbo.spSaveXMLVariableToFile') IS NOT NULL
   DROP PROCEDURE dbo.spSaveXMLVariableToFile
GO
CREATE PROCEDURE dbo.spSaveXMLVariableToFile
  @TheXML XML,
  @Filename VARCHAR(255)
AS
  SET NOCOUNT ON
  DECLARE @MySpecialTempTable VARCHAR(255)
  DECLARE @Command NVARCHAR(4000)
  DECLARE @RESULT INT

--firstly we create a global temp table with a unique name
  SELECT  @MySpecialTempTable = '##temp'
       + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
--then we create it using dynamic SQL, & insert a single row
--in it with the variable stocked with the XML we want
  SELECT  @Command = 'create table ['
       + @MySpecialTempTable
       + '] (MyID int identity(1,1), Bulkcol XML)
insert into ['
       + @MySpecialTempTable
       + '](BulkCol) select @TheXML'
  EXECUTE sp_ExecuteSQL @command, N'@TheXML XML',
           @TheXML

--then we execute the BCP to save the file
  SELECT  @Command = 'bcp "select BulkCol from ['
          + @MySpecialTempTable + ']'
          + '" queryout '
          + @Filename + ' '
         + '-w'--save as unicode
          + ' -T -S' + @@servername
  EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT
  EXECUTE ( 'Drop table ' + @MySpecialTempTable )
  RETURN @result
GO 
-- test generic procedure
--we write it out to a unicode file
EXECUTE dbo.spSaveXMLVariableToFile @testXML, 'D:\files\SampleXML'  
--and read it back in again!    
    SELECT CONVERT(nVARCHAR(max),BulkColumn)
FROM    OPENROWSET(BULK 'D:\files\SampleXML', SINGLE_BLOB) AS x 
more ▼

answered May 04, 2011 at 05:50 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

Thank you very much for the effort. This indeed is a very good method.
May 25, 2011 at 01:03 PM Slick84
(comments are locked)
10|1200 characters needed characters left

Check out the CAMEditor on SourceForge.net - it has drag and drop tools for generating XML instances from SQL tables - can be run interactively or standalone.

You design the XML structure / mapping you want visually - then the tool builds all the SQL needed - extracts the data and creates the output.

http://www.cameditor.org

There is also video on Youtube demonstrating this in action.

Enjoy.
more ▼

answered Oct 08, 2012 at 02:36 PM

xmlorb gravatar image

xmlorb
8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x939
x150
x6

asked: Apr 27, 2010 at 12:25 PM

Seen: 43911 times

Last Updated: Oct 08, 2012 at 02:36 PM