Output SQL data as XML and export to XML file

Question

question

Slick84 asked Apr 27, '10

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

ssis t-sql xml data-transfer

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · Apr 28, 2010 at 05:51 PM

Great answers to this question. Hope this helps others as well.

0 ·

Answer 1 · 2010-04-27T12:54:01Z

Oleg answered Apr 27, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-04-27T13:07:10Z

Bhaskar answered Apr 27, '10

Another good article here

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2011-05-04T05:50:10Z

Phil Factor answered May 4, '11

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='BloodrootSanguinaria canadensis4Mostly Shady$2.44031599ColumbineAquilegia canadensis3Mostly Shady$9.37030699Marsh MarigoldCaltha palustris4Mostly Sunny$6.81051799CowslipCaltha palustris4Mostly Shady$9.90030699Dutchman''s-BreechesDicentra cucullaria3Mostly Shady$6.44012099Ginger, WildAsarum canadense3Mostly Shady$9.03041899HepaticaHepatica americana4Mostly Shady$4.45012699LiverleafHepatica americana4Mostly Shady$3.99010299Jack-In-The-PulpitArisaema triphyllum4Mostly Shady$3.23020199MayapplePodophyllum peltatum3Mostly Shady$2.98060599Phlox, WoodlandPhlox divaricata3Sun or Shade$2.80012299Phlox, BluePhlox divaricata3Sun or Shade$5.59021699Spring-BeautyClaytonia Virginica7Mostly Shady$6.59020199TrilliumTrillium grandiflorum5Sun or Shade$3.90042999Wake RobinTrillium grandiflorum5Sun or Shade$3.20022199Violet, Dog-ToothErythronium americanum4Shade$9.04020199Trout LilyErythronium americanum4Shade$6.94032499Adder''s-TongueErythronium americanum4Shade$9.58041399AnemoneAnemone blanda6Mostly Shady$8.86122698Grecian WindflowerAnemone blanda6Mostly Shady$9.16071099Bee BalmMonarda didyma4Shade$4.59050399BergamotMonarda didyma4Shade$7.16042799Black-Eyed SusanRudbeckia hirtaAnnualSunny$9.80061899ButtercupRanunculus4Shade$2.57061099CrowfootRanunculus4Shade$9.34040399Butterfly WeedAsclepias tuberosaAnnualSunny$2.78063099CinquefoilPotentillaAnnualShade$7.06052599PrimroseOenothera3 - 5Sunny$6.56013099GentianGentiana4Sun or Shade$7.81051899Blue GentianGentiana4Sun or Shade$8.56050299Jacob''s LadderPolemonium caeruleumAnnualShade$9.26022199Greek ValerianPolemonium caeruleumAnnualShade$4.36071499California PoppyEschscholzia californicaAnnualSun$7.89032799Shooting StarDodecatheonAnnualMostly Shady$8.60051399SnakerootCimicifugaAnnualShade$5.63071199Cardinal FlowerLobelia cardinalis2Shade$3.02022299' -- 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

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · May 25, 2011 at 01:03 PM

Thank you very much for the effort. This indeed is a very good method.

0 ·

question