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

Without any manual intervention it means to write a client-side program that runs the query, gets the result and saves it as a file. — Alejandro
– Alejandro, Commented Jul 8, 2021 at 16:20
Is there any documentation in order to understand how I can start to? Thank you! — Fuboski
– Fuboski, Commented Jul 8, 2021 at 16:21
You can look at the docs of your favorite programming language on how to issue SQL Server queries, and then on how to save text files. It depends on how you start the project with. — Alejandro
– Alejandro, Commented Jul 8, 2021 at 16:31
You can use a bcp utility to generate an XML file on a file system. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jul 8, 2021 at 19:48
Can you link me something? I have already tried it with a bcp (following some answer in stackoverflow) but i can't understand how it works — Fuboski
– Fuboski, Commented Jul 9, 2021 at 7:44

Naman · Accepted Answer · 2021-07-08 16:11:17Z

0

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.

answered Jul 8, 2021 at 16:11

Naman

3062 silver badges10 bronze badges

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

1 Comment

Fuboski Over a year ago

Thank you @Naman but I would like to avoid to save every time the query

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related