Exporting XML to a File

Activate xp_cmdsell

EXEC sp_configure 'show advanced options', 1

GO

exec sp_configure'xp_cmdshell',1

go

reconfigure

go

Create a sample table

CREATE TABLE MyXMLTable

(

xCol XML

) ;

Sample script

Make sure you are connected to the Northwind sample datase

Make sure you have a d:\ drive or change the path of the export file

DECLARE @testXML XML

SELECT @TestXML=

(

Select EmployeeId '@KodiPunonjesit',Firstname 'Gjeneralitet/Emri',Lastname 'Gjeneralitet/Mbiemri',

(

SELECT TOP 2 Orders.OrderID '@kodi',

Orders.OrderDate '@Data',

Orders.CustomerID 'Klienti/@Kodi'

FROM Orders

where Orders.EmployeeID=Employees.EmployeeID

FOR XML PATH('Porosi'),TYPE,ROOT('Porosite')

)

From Employees

where EmployeeID <3

FOR XML PATH('Punonjes'),ROOT('Punonjesit')

)

-- We Store the contents of an XML variable to a table

truncate table MyXMLTable;

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:\TestXMLRoutine.xml'

/* 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:\TestXMLRoutine.xml', SINGLE_BLOB) AS x

--but we can turn this into a generic procedure

go