How To Export Xml Using A Sql Server Query?

Let's say I have a table Employee like this EmpID, EmpName 1 , hatem and I write a query: select * from Employee for xml auto so the output will be in XML format. I want to kn

Solution 1:

If you only need to store the XML and not do anything else to it, this is probably the easiest way to accomplish this - using straight 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 a file D:\test.xml (or change that to match your system) and will put those XML tags into that file.

The SqlCommand object also has a .ExecuteXmlReader() method which would return an XmlReader object to scan and manipulate the XML - not just return a string. Use whatever makes the most sense to you!

PS: also, the output of FOR XML AUTO is a bit .... let's say ... suboptimal. It uses the dbo.Employee as it's main XML tag and so forth... with SQL Server 2008, I would strongly recommend you look into using FOR XML PATH instead - it allows you to tweak and customize the layout of the XML output.

Compare your original XML 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')

Output is:

<Employees><EmployeeID="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><EmployeeID="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>

Solution 2:

I've had the same problem and I've 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;


publicsealedclassStringWriterWithEncoding : StringWriter
{
    privatereadonly Encoding encoding;

    publicStringWriterWithEncoding(Encoding encoding)
    {
        this.encoding = encoding;
    }

    publicoverride Encoding Encoding
    {
        get { return encoding; }
    }
}

publicpartialclassStoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    publicstaticvoidXMLExport (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
            {
                thrownew Exception("Parameters must be set");
            }
        }
        catch
        {
            throw;
        }
    }
}

Here's an example 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 output is a nicely formatted XML file:

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