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

Question

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')

Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio? — har07
– har07, Commented Sep 13, 2016 at 4:26

Gottfried Lesigang · Accepted Answer · 2016-09-13 07:05:10Z

1

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;

answered Sep 13, 2016 at 7:05

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Steve Tianqin Guo Over a year ago

Sir,Thank you so much fro the reply. Can I add multiple select above +queryout ?

Gottfried Lesigang Over a year ago

@SteveTianqinGuo This should work with any SELECT which works on its own... It might be better (better to read and better to maintain) to create a VIEW or a FUNCTION and reduce the call within the BCP-command to a simple 'bcp.exe database.schema.ViewName out ...'. Note that a simple out instead of your queryout does not expect a SELECT but the name of an existing table or VIEW...

Steve Tianqin Guo Over a year ago

I am really appreciate.Thanks! I turn on the BCP and use the following code:

Steve Tianqin Guo Over a year ago

I did what you said. I have the error SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file

Gottfried Lesigang Over a year ago

@SteveTianqinGuo The filename you specify must be a complete and valid path ending with the file name. You might read this

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related