Importing XML into Excel over HTTP

Question

Hi,

I've got a simple (inline) ASPX page that is ouputting XML.

I want to import this XML into Excel 2007 using Data | From Other Sources | From XML Data Import.

If I specify the URL for my APSX page Excel opens the result into the Text Import Wizard :(

If I open my URL directly from Notepad, save the content to a file and import that into Excel, its correctly treating is a Xml.

Can anybody suggest any reason why Excel is treating the result as text when is comes from a Url?

Heres the code from the ASPX Page

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Text" %>
<%
    Response.Clear();
    Response.ContentType = "text/xml";
    Response.ContentEncoding = Encoding.UTF8;

    XmlWriterSettings settings = new XmlWriterSettings();
    settings.Indent = true;
    settings.Encoding = Encoding.UTF8;
    settings.CloseOutput = false;
    settings.CheckCharacters = true;

    using (MemoryStream output = new MemoryStream())
    {
        using (XmlWriter xml = XmlWriter.Create(output, settings))
        {
            xml.WriteStartDocument();

blah blah.. build XML here

            xml.WriteEndElement();
            xml.Flush();
            xml.Close();
        }
        Response.Write(Encoding.UTF8.GetString(output.ToArray()));
    }

    Response.End(); 
%>

Answer 1

This is a wild guess, but have you tried to send the response using a different content type?

I would give it a shot with one of the following MIME types:

• application/xml
• application/vnd.ms-excel
• application/x-excel
• application/x-msexcel

Related resources:

• How to format an Excel workbook while streaming MIME content