Menu
I am working on an application where I need to get the SQL response as XML into an XML file (and to store it in some physical location, say c:\xyz.xml).
I am able to generate the XML content using the provisions available in SQL Server as shown below.
SELECT * FROM @Table FOR XML AUTO, ELEMENTS
where: @Table is a table variable.
I want to know how I can store the query output to an XML file from SQL Server itself.
699
With SQL Server Management Studio, you can save your SQL database as CSV and then you can convert the database files to XML, PDF or other formats as you like.
Here we are going to create an XML file from Database. Make an SQL connection to the Database and execute the sql and store the data in a Datset. Call Dataset's WriteXml() method and pass the file name as argument. You have to pass necessary database connection information to connection string.
Create columns and variablesCREATE TABLE T1(Col1 int primary key, Col2 xml); You can use a DECLARE statement to create a variable of xml type, as the following example shows. DECLARE @x xml; Create a typed xml variable by specifying an XML schema collection, as shown in the following example.
There's one more option - use sqlcmd tool.
:XML ON as a first line in your SQL file (let's call it input.sql)sqlcmd -S <your-server> -i input.sql -o output.xml
100
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
