How can I save an XML file from a SQL query?

Question

I'm using SQL Server 2019 - v15.0.4123.1.

This is the sample query (from this question):

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, city VARCHAR(30))

INSERT INTO @tbl (city) 
VALUES ('Miami'), ('Orlando');

SELECT 
    'SIN_OPE' AS [@cod_1], 
    '08' AS [@cod_2], 
    '12' AS [@num_reg],
    'yyyyyyyyyyyyyyyyyyy.xsd' AS [@xsi:noNamespaceSchemaLocation],
    (SELECT * 
     FROM @tbl
     FOR XML PATH('r'), TYPE)
FOR XML PATH('root'), TYPE, ELEMENTS XSINIL;

The result of the query is:

I just want to save/export this file as an XML file, automatically (with a stored procedure) to a folder in my PC. I've found many possible solutions but they are pretty old and doesn't work with my environment. I still can't understand if it's possible to export XML from SQL Server. I would like to avoid the manual passage "Query - Results to - Results to file"

Any advice?

Thank you

Answer 1

On you SQL Server Management Tool go to Query --> Result --> Result to file and then execute your query.

once you execute the query it will open a window & give you an option to save the file, there you can save it as xml.