在Web API C#中使用FOR XML从SQL中读取XML结果。

using System.Data.SqlClient;
using System.Xml;

public string GetXmlDataFromSql()
{
    string connectionString = "YourConnectionString";
    string sqlQuery = "SELECT * FROM YourTable FOR XML AUTO, ROOT('Root')";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlCommand command = new SqlCommand(sqlQuery, connection))
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    // 创建XmlWriter对象来构建XML文档
                    XmlWriterSettings settings = new XmlWriterSettings();
                    settings.Indent = true;
                    settings.NewLineChars = "\r\n";
                    settings.NewLineHandling = NewLineHandling.Replace;

                    using (XmlWriter writer = XmlWriter.Create("output.xml", settings))
                    {
                        // 将查询结果转换为XML格式
                        writer.WriteStartDocument();
                        writer.WriteStartElement("Root");

                        while (reader.Read())
                        {
                            // 根据需要读取并写入XML节点
                            // 例如：writer.WriteElementString("ColumnName", reader["ColumnName"].ToString());
                        }

                        writer.WriteEndElement();
                        writer.WriteEndDocument();
                    }
                }
            }
        }
    }

    return "XML data has been retrieved and saved to output.xml.";
}