How to automate XML output to a shared folder?

Transact-SQL
1

Hello all,

I have a query that output to XML format based on my sql statement as listed below.

SELECT	TOP 10
		[RowNum]
		,ISNULL([Dependent],'') AS [Dependent]
		,ISNULL([Occupation],'') AS Occpation
		,[Membership_Start_Date]
		,[Membership_Fee]
		,ISNULL(CONVERT(VARCHAR(10),[Membership_End_Date],101),'') AS [Membership_End_Date]
FROM [MyDatabase].[dbo].[Health_Program_Membership]

And the return values are:

Health%20Return%20Values

Then I got my xml convertion query as:

SELECT	TOP 10
		[RowNum]
		,ISNULL([Dependent],'') AS [Dependent]
		,ISNULL([Occupation],'') AS Occpation
		,[Membership_Start_Date]
		,[Membership_Fee]
		,ISNULL(CONVERT(VARCHAR(10),[Membership_End_Date],101),'') AS [Membership_End_Date]
FROM [MyDatabase].[dbo].[Health_Program_Membership]
FOR XML PATH (''), ROOT ('Health_Membership')

and returned as:

XML%20return%20values

I can go to "Save as" on the "File" menu from SSMS to save this XML file format by clicking the xml path from the result set and put it in a local folder, but I am trying to auto this process.

What I am trying to do is when I execute my sql statement that returns the XML output, instead for me to click the link to see the XML file, I wanted to automatically save it as a xml file in my local folder.

XML%20return%20values2
XML return values2.jpg869×420 104 KB

XML%20return%20values3
XML return values3.jpg684×612 49.1 KB

Anyone?

Thanks all

2

Research these following possible options

  1. powershell
  2. ssis/sql job
  3. bat file calling dtxec
  4. python
  5. n scripting language