How to get XML data from a column in an SQL table?

Question

I have a table in SQL and one of its column is an XML datatype. How will I be able to get its value from SQL to ASP.NET (C#) code-behind? I want to store it as an Xml type and not as string type because I need to manipulate the values and the attributes inside of it. I tried getting it and putting it on a string type variable and what I get is something like this: AAYYYNYNYNStarLight. I would want to get this instead:

<ANSWERS>
<Answer item="ddl_3">A</Answer>
<Answer item="ddl_8">A</Answer>
<Answer item="ddl_13">Y</Answer>
<Answer item="ddl_16">Y</Answer>
<Answer item="ddl_19">Y</Answer>
<Answer item="ddl_22">N</Answer>
<Answer item="ddl_26">Y</Answer>
<Answer item="ddl_30">N</Answer>
<Answer item="ddl_34">Y</Answer>
<Answer item="ddl_38">N</Answer>
<Answer item="ddl_42">StarLight</Answer>
</ANSWERS>

How can I do it and be able to manipulate each node? I'm a complete noob about XML and any help will be much appreaciated. :)

Edit: cleaned up the xml

Answer 1

I'm assuming you're getting data out of SQL Server using straight up ADO.NET and not an ORM like Entity Framework. If I'm reading the documentation here correctly, the XML columns are exposed as an XmlReader that you can retrieve using the GetSqlXml method.

Once you have an XmlReader you can load it directly into an XDocument using the Load method.

Another thing you might be interested in is that there are two different XML namespaces, take a look at this question about the difference between XDocument and XmlDocument.

Answer 2

If you do something like this in your C# code, you will definitely read the XML properly from your database:

// setup connection string and query statement
string connStr = "server=(local);database=Test;integrated security=SSPI;";
string query = "SELECT XmlContent FROM dbo.XmlTest2 WHERE ID = @ID";

// wrap your SqlConnection and SqlCommand in using blocks...
using(SqlConnection _con = new SqlConnection(connStr))
using(SqlCommand _cmd = new SqlCommand(query, _con))
{
   // setup parameter for _cmd
   _cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1;

   _con.Open();
   string contentsOfYourXml = (string)_cmd.ExecuteScalar();
   _con.Close();
}

When you put this string onto an ASP.NET page, however, the ASP.NET runtime will attempt an XSL transformation, and by default, if you don't specify anything, XSLT will just simply return all element values --> that's the string your seeing displayed.

But reading from the database most definitely works just fine! Once you have that string containing all your XML, you can do with it whatever you like - manipulate it with Linq-to-XML or whatever.

Answer 3

Well, after a few more intense research, I was able to answer my own question. :D

Here is code:

private void ddl_Eval_SelectedIndexChanged (Object sender, System.EventArgs e)
{
    //Response.Write(ddl_Eval.SelectedValue.ToString() + " " + "value: " + ddl_Eval.SelectedIndex);

    dtEval = new DataTable();
    dtEval = data.GetEvaluation2();
    DataView dvEval = dtEval.DefaultView;

    string xmlDoc = String.Empty;

    foreach (DataRowView drvEval in dvEval)
    {
        if (drvEval.Row["EvaluationID"].ToString() == ddl_Eval.SelectedValue.ToString())
        {
            xmlDoc = drvEval.Row["EvalAnswers"].ToString();
        }
    }

    XDocument xdoc = new XDocument();
    xdoc = XDocument.Parse(xmlDoc);

    foreach (XElement child in xdoc.Root.Elements("Answer"))
    {
        Response.Write(child.Attribute("item").Value + " " + child.Value + "<br />");
    }

}

Now, I'm able to get the value of Answer and the value of the attribute item. :D