SQL Server 返回的 XML 格式 [英] Format XML Returned from SQL Server

问题描述

我有一个在 Visual Studio 中编写的程序.代码如下:

I have a program that I have written in Visual Studio. Here is the code:

namespace SQL_Connectivity
{
   class Program
   {
       static void Main(string[] args)
       {
           SqlConnection conn = new SqlConnection("Server=************.database.windows.net,0000;Database=testdb;User ID=testuser;Password= testpassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;");
        conn.Open();
           SqlCommand cmd = new SqlCommand("SELECT * FROM District WHERE leaID <= 4 FOR XML PATH('districtEntry'), ROOT('districts')", conn);
           SqlDataReader reader = cmd.ExecuteReader();
           string path = @"District." + DateTime.Now.ToString("yyyyMMdd") + ".xml";
           var writer = new StreamWriter(path);
           while(reader.Read())
           {
               Console.SetOut(writer);
               Console.WriteLine(reader.GetString(0));

           }

           reader.Close();
           conn.Close();
       }
   }
}

假设 SqlConnection 字符串有效.查询继续查询数据库并以完美的格式返回结果.但是，当它通过我的代码时，它会保存在一个 XML 文件中，如下所示:

Assume that the SqlConnection string is valid. The query goes on to query the database and return the results in perfect format. However, when it goes through my code, it gets saved in a XML file which looks like this:

<districts><districtEntry><leaID>01</leaID><name>Alachua</name>    <organizationType>LEA</organizationType><streetLine1>620 E University     Ave</streetLine1><city>Gainesville</city><stateProvince>FL</stateProvince><postalCode>326015448</postalCode><county>Alachua</county><NCESID_district>1200030</NCESID_district><date_created>2015-06-01T20:38:58.9730000</date_created><date_updated>2015-06-01T20:38:58.9730000</date_updated></districtEntry><districtEntry><leaID>02</leaID><name>Baker</name><organizationType>LEA</organizationType><streetLine1>270 South Blvd E</streetLine1><city>MacClenny</city><stateProvince>FL</stateProvince><postalCode>320632538</postalCode><county>Baker</county><NCESID_district>1200060</NCESID_district><date_created>2015-06-01T20:38:58.9730000</date_created><date_updated>2015-06-01T20:38:58.9730000</date_updated></districtEntry><districtEntry><leaID>03</leaID><name>Bay</name><organizationType>LEA</organizationType><streetLine1>1311 Balboa Ave</streetLine1><city>Panama City</city><stateProvince>FL</st

这一切都以一行结束，而且我的代码没有完成 XML 文件(最后缺少数据).它似乎总是停在表中最后一行之前的行.我也用多个表尝试过这个.

It all ends up on one row, AND my code does not finish the XML file (missing data at the end). It seems to always stop at the row before the last in the table. I have also tried this with multiple tables.

我在问是否有办法

1. 格式化我的 XML 文件以使其更好 &在单独的行上整齐.
2. 调整我的代码，以便将查询结果准确地保存到 XML 文件中.

推荐答案

以下代码解决了您的原始代码和您的答案的几个问题:

The following code solves several of the problems with your original code and your answer:

private static void Main(string[] args)
{
    using (
        var conn =
            new SqlConnection(
                "Server=************.database.windows.net,0000;Database=testdb;User ID=testuser;Password= testpassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
        )
    {
        conn.Open();
        using (
            var cmd =
                new SqlCommand(
                    "SELECT * FROM District WHERE leaID <= 4 FOR XML PATH('districtEntry'), ROOT('districts')",
                    conn))
        {
            using (var reader = cmd.ExecuteXmlReader())
            {
                var doc = XDocument.Load(reader);
                string path = @"District." + DateTime.Now.ToString("yyyyMMdd") + ".xml";
                using (var writer = new StreamWriter(path))
                {
                    doc.Save(writer);
                }
            }
        }
    }
}

首先，SqlConnection、SqlCommand、XmlReader和StreamWriter都需要在using 块，以确保它们被清理干净，即使抛出异常.

First, the SqlConnection, SqlCommand, XmlReader and StreamWriter all need to be in using blocks, in order to ensure that they are cleaned up, even if an exception is thrown.

其次，DataSet 无法处理所有可能的 XML 文档.它只能处理可以表示为关系"表的那些.当您只想解析 XML 时，构建表和列也是不必要的开销.我使用 XDocument 代替.

Second, DataSet cannot process all possible XML documents. It can only process those which could be represented as "relational" tables. It is also unnecessary overhead to build out tables and columns when all you want to do is parse XML. I use XDocument instead.

最后，XDocument.Save 方法用缩进写出 XML.

Finally, the XDocument.Save method writes out the XML with indentation.

这篇关于SQL Server 返回的 XML 格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！