3

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')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steve Tianqin Guo
  • 83
  • 6
  • [Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?](http://stackoverflow.com/questions/6354130/is-there-a-select-into-outfile-equivalent-in-sql-server-management-studio) – har07 Sep 13 '16 at 04:26

1 Answers1

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;
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Sir,Thank you so much fro the reply. Can I add multiple select above +queryout ? – Steve Tianqin Guo Sep 13 '16 at 13:44
  • @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`... – Shnugo Sep 13 '16 at 13:52
  • I am really appreciate.Thanks! I turn on the BCP and use the following code: – Steve Tianqin Guo Sep 13 '16 at 14:47
  • 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 – Steve Tianqin Guo Sep 13 '16 at 14:49
  • @SteveTianqinGuo The filename you specify must be a complete and valid path ending with the file name. You might [read this](http://stackoverflow.com/a/39465418/5089204) – Shnugo Sep 13 '16 at 14:58
  • Sir, I just want to say may sincerely thank you. Thank you so much. I fixed this problem and I am really appreciate. – Steve Tianqin Guo Sep 13 '16 at 17:47
  • @SteveTianqinGuo I'm glad to read this! But please allow me one hint: It is very kind of you to say *Thank you*, but it would be even kinder, to tick the acceptance check below the answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Once you've crossed the 15 points border yourself, you are asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! – Shnugo Sep 14 '16 at 06:25