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

score 2 · Answer 1 · edited May 23 '17 at 12:09

2

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.

edited May 23 '17 at 12:09

Community

1
1

answered Apr 28 '11 at 02:12

Roman

19,581
6
68
84

I checked the link about that XDocument.Parse but then it only accepts string-type as a parameter. And the column I have is an Xml type. If I store that from XML(SQL datatype) to string xmlValue, all the xml tags will be gone. So when I tried using XDocument.Parse(xmlValue), it throws an error "The string was not recognized as a valid Uri." – Rome Apr 28 '11 at 03:53
I actually have a problem using that XmlReader because we have a separate class for all data access. When I'm trying that method I'm getting "invalid attempt to read when reader is closed". I think that is because after the data access class returns the xml result to code-behind, the data access class closes the XmlReader. Anyway, thanks for telling me about XDocument. It gave me an alternative solution. I will store the XML document on SQL as a string instead of XML type (which I know is not a very elegant solution). Then, when I retrieve it, I will use XDocument.Parse :D – Rome Apr 28 '11 at 05:51
@Rome: Do you have any control over the data access classes? Could you change them to read the XML data properly and return an xml document instead of a string? – Roman Apr 28 '11 at 13:35
You could use XDocument.Load(MyDataReader.GetSqlXml(i).CreateReader()) instead of .Parse. – Sam Nov 25 '13 at 13:17

score 2 · Answer 2 · answered Apr 28 '11 at 06:33

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.

Beware that ExecuteScalar only reads 2033 characters. Everything above is cutted! — ingbabic, Sep 04 '21 at 12:57

score 2 · Answer 3 · answered Apr 29 '11 at 00:09

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

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

3 Answers3

Linked