如何从一个SQL表中的列获取XML数据？ [英] How to get XML data from a column in an SQL table?

问题描述

我有一个表在SQL和其列中的一个是一个XML数据类型。我如何能够从SQL它的价值得到ASP.NET（C＃）code-背后？我想将它保存为XML类型而不是字符串类型，因为我需要操作的价值和它里面的属性。我试图得到它，​​并把它放在一个字符串类型的变量和我所得到的是这样的：AAYYYNYNYNStarLight。我希望得到这个代替：

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>

我怎么能做到这一点，并能操纵每个节点？我对XML的一个完整的小白和任何帮助将非常AP preaciated。 ：）

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. :)

编辑：清理XML

推荐答案

如果您在C＃code做这样的事情，你一定会正确地从数据库中读取XML：

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();
}

当你把这个字符串到ASP.NET页面，但是，ASP.NET运行时将尝试XSL转换，默认情况下，如果不指定任何东西，XSLT将只是简单地返回所有的元素值 - >这是显示在您看到的字符串。

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.

但是，从数据库中读取最肯定的作品就好了！一旦你有一个包含所有的XML该字符串，你可以用它做任何你喜欢的 - 使用LINQ到XML或任何操纵它

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.

这篇关于如何从一个SQL表中的列获取XML数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！