Simplest way to output XML from SQL Server to client

Question

I need to return contents of an xml column in sql server to client by simply clicking a URL. The xml has processing instruction to open the correct application (Infopath in this case).

I'm a bit confused as to all the various xmlreader writers and streams available. I suppose I want to send the contents of SQLs ExecuteXmlReader to Response.OutputStream - but am unsure how to plumb them together.

Thanks is advance

Answer 1

I would suggest getting the XML from the database as text, then creating a page dynamically and set the MIME type in the header to "application/xml". This should tell the browser to DTRT (assuming it's configured correctly).

If you're not working with a web application, then you might want to consider using REs to parse the text and determine the application to launch, then feed it the XML (either using a temp file or via input redirection of some type).

Since you don't give any indication of what technologies you're using beyond XML and SQL Server, it's hard to be really specific.

Answer 2

Why don't you just query the database using standard SQL for the contents of the XML column, and grab that in a page and stream that string back to the user?

Something like:

string xmlContents = string.Empty;

using(SqlConnection _con = new SqlConnection("server=(local);database=Northwind;Integrated Security=SSPI;")
{
  string queryStatement = "SELECT XmlColumn FROM dbo.MyTable WHERE id = @ID";

  using(SqlCommand _cmd = new SqlCommand(queryStatement, _con)
  {
    _cmd.Parameters.AddWithValue("@ID", 5555);

    _con.Open();

    xmlContents = _cmd.ExecuteScalar().ToString(); // returns an object
    _con.Close();
  }
}

and then just stream back the XML to the user, e.g. using Response.Write(xmlContents) - something roughly along those lines (no error handling included - I'll leave that up to you to handle :-) ).

Does that help any??

Marc

Answer 3

SQL 2005 and beyond support the FOR XML statement. See here

For example:

SELECT * FROM MyTable FOR XML RAW;

There are other keywords for tweaking the XML tag names to be different from the datrabase columns as well.

Then look at the ExecuteXmlReader documentation for whatever Command class you use in C#.

Answer 4

I got it working by simply using Response.Write with the xml as text, but I'm still not sure if I should really be using XmlReaders and streams. Here's what I did:

SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Name, InfopathDoc from document where id = @id";
cmd.Parameters.AddWithValue("@id", docId);

using (SqlDataReader dr = cmd.ExecuteReader())
{
    if (dr.Read())
    {
        Response.ContentType = "application/xml";
        Response.AppendHeader("Content-disposition", "attachment; filename=" + dr["Name"]);
        Response.Write(dr["InfopathDoc"]);
    }