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