Output SQL data as XML and export to XML file

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

Another good article here

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/create-xml-files-out-of-sql-server-with-

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 

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.