CODEWITHSUNDEEP
sql-serverxmlt-sql
Steve Tianqin Guo
Steve Tianqin Guo

Reputation: 83

Generate XML from SQL Server by select multiple tables to a certain file location

If I have following SQL statement which I can actually use to generate XML by the data from two tables. I got the results, but I have to right click it, click "Save As," then choose a location (e.g. C:\Users\my\Documents) for saving this XML. Is there a way to automate this?

SELECT
    (SELECT y.* FROM dbo.TableY FOR XML PATH('y'), TYPE) AS 'YElements',
    (SELECT a.* FROM dbo.TableA FOR XML PATH('a'), TYPE) AS 'AElements'
FOR XML PATH(''), ROOT('root')

Upvotes: 3

Views: 314

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You can use BCP, but you might have to enable XP_CMDSHELL using SP_CONFIGURE...

Furthermore there are some riddles to solve, as BCP has some rather weird attitudes (escaping characters, internal multiple quotes, fully qualified names...).

The main idea is:

  • Build a dynamic SQL statement to allow dynamically filled in pieces like a file name (but you might hardcode this...)
  • Execute the statement with xp_cmdshell

This will - at least - show you the general approach: 

DECLARE @FileName VARCHAR(50)='C:\Users\...';
DECLARE @SQLCmd   VARCHAR(2000)=
(
    SELECT 'bcp.exe ' 
         + '"SELECT ''Just a test'' AS TestColumn FOR XML PATH(''''), ROOT(''root'')"' 
         + ' queryout '  
         + @FileName 
         + ' -w -T -S ' + @@SERVERNAME
);
-- display command, for visual  check
SELECT @SQLCmd AS 'Command to execute'

-- create the XML file
EXEC xp_cmdshell @SQLCmd;

Upvotes: 1

Related Questions