Export xml from sql server

Question

Say I have an Employee table like this

 EmpID, EmpName 1 , hatem 

and I write a query: select * from Employee for xml auto

therefore, the output will be in xml format

I need help to find out how I can export the result to an xml file that will be saved on my computer (HD) since I need to read the XML files from this folder and deserialize them in my .net application.

Answer 1

If you only need to save the XML and not do anything else, this is probably the easiest way to do this - using a simple simple ADO.NET:

 string query = "SELECT EmployeeID, LastName, FirstName, Title, BirthDate, HireDate FROM dbo.Employees FOR XML AUTO"; using(SqlConnection _con = new SqlConnection("server=(local);database=Northwind;integrated security=SSPI;")) using (SqlCommand _cmd = new SqlCommand(query, _con)) { _con.Open(); string result = _cmd.ExecuteScalar().ToString(); _con.Close(); File.WriteAllText(@"D:\test.xml", result); } 

This will create the file D:\test.xml (or modify it according to your system) and place these XML tags in this file.

The SqlCommand object also has a .ExecuteXmlReader() method, which returns an XmlReader object for scanning and manipulating XML - it does not just return a string. Use what suits you best!

PS: also the output of FOR XML AUTO bit ... let ... suboptimal. It uses dbo.Employee as the main XML tag, etc ... with SQL Server 2008, I would strongly recommend that you use FOR XML PATH instead - it allows you to customize and customize the XML output layout.

Compare XML source output with FOR XML AUTO

 <dbo.Employees _x0040_ID="1" LastName="Davolio" FirstName="Nancy" Title="Sales Representative" BirthDate="1948-12-08T00:00:00" HireDate="1992-05-01T00:00:00" /> <dbo.Employees _x0040_ID="2" LastName="Fuller" FirstName="Andrew" Title="Vice President, Sales" BirthDate="1952-02-19T00:00:00" HireDate="1992-08-14T00:00:00" /> 

against this query - just to see the difference:

 SELECT [EmployeeID] AS '@ID', [LastName], [FirstName], [Title], [BirthDate], [HireDate] FROM [dbo].[Employees] FOR XML PATH('Employee'), ROOT('Employees') 

Exit:

 <Employees> <Employee ID="1"> <LastName>Davolio</LastName> <FirstName>Nancy</FirstName> <Title>Sales Representative</Title> <BirthDate>1948-12-08T00:00:00</BirthDate> <HireDate>1992-05-01T00:00:00</HireDate> </Employee> <Employee ID="2"> <LastName>Fuller</LastName> <FirstName>Andrew</FirstName> <Title>Vice President, Sales</Title> <BirthDate>1952-02-19T00:00:00</BirthDate> <HireDate>1992-08-14T00:00:00</HireDate> </Employee> 

Answer 2

I had the same problem and I created a .NET CLR that exports XML to a file:

 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Text; using System.Xml; using System.IO; public sealed class StringWriterWithEncoding : StringWriter { private readonly Encoding encoding; public StringWriterWithEncoding(Encoding encoding) { this.encoding = encoding; } public override Encoding Encoding { get { return encoding; } } } public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void XMLExport (SqlXml InputXml, SqlString OutputFile) { try { if (!InputXml.IsNull && !OutputFile.IsNull) { XmlDocument doc = new XmlDocument(); doc.LoadXml(InputXml.Value); StringWriterWithEncoding sw = new StringWriterWithEncoding(System.Text.Encoding.UTF8); XmlWriterSettings settings = new XmlWriterSettings { Indent = true, IndentChars = " ", NewLineChars = "\r\n", NewLineHandling = NewLineHandling.Replace, Encoding = System.Text.Encoding.UTF8 }; using (XmlWriter writer = XmlWriter.Create(sw, settings)) { doc.Save(writer); } System.IO.File.WriteAllText(OutputFile.ToString(), sw.ToString(), System.Text.Encoding.UTF8); } else { throw new Exception("Parameters must be set"); } } catch { throw; } } } 

Here is an example of how to use it:

 DECLARE @x xml SET @x = '<Test><Something>1</Something><AnotherOne>2</AnotherOne></Test>' EXEC dbo.XmlExport @x, 'c:\test.xml' 

And the result is a well formatted XML file:

 <?xml version="1.0" encoding="utf-8"?> <Test> <Something>1</Something> <AnotherOne>2</AnotherOne> </Test>