CODEWITHSUNDEEP
c#sql-serverxml
Brandon Lumsden
Brandon Lumsden

Reputation: 79

Format XML Returned from SQL Server

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

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

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.

I am asking if there is a way to

  1. Format my XML file to make it nice & neat on separate lines.
  2. Adjust my code so that the results from the query are saved exactly how it is into the XML file.

Upvotes: 0

Views: 4090

Answers (3)

John Saunders
John Saunders

Reputation: 161773

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

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.

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.

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

Upvotes: 2

Brandon Lumsden
Brandon Lumsden

Reputation: 79

I have come across a solution & here is the code that I changed it to:

        SqlCommand cmd = new SqlCommand("SELECT * FROM District FOR XML PATH('districtEntry'), ROOT('districts')", conn);
        string path = @"District." + DateTime.Now.ToString("yyyyMMdd") + ".xml";
        XmlReader reader = cmd.ExecuteXmlReader();
        var data = new DataSet();
        data.ReadXml(reader);
        data.WriteXml(path);

I learned that the reader.Read() was reading the file line by line and writing it to the file.

Upvotes: 1

Cadburry
Cadburry

Reputation: 1864

You have to use reader.GetSqlXml to get the result. Take a look at What does the SQL Server XML datatype translate to in .NET and how do I convert it to XmlDocument?

Upvotes: 1

Related Questions