Format XML Returned from SQL Server

Question

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

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

Does reader.Getstring(0) return the right result in the debugpreview at runtime — Cadburry
– Cadburry, Commented Jun 2, 2015 at 22:16
@har07 Yeah sorry, I meant to say that I was using Visual Studio to write C#. It was a long day. — Brandon Lumsden
– Brandon Lumsden, Commented Jun 3, 2015 at 14:49

John Saunders · Accepted Answer · 2015-06-04 00:58:12Z

2

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.

answered Jun 4, 2015 at 0:58

John Saunders

162k26 gold badges252 silver badges403 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brandon Lumsden Over a year ago

so are the using statements functioning like try...catch in Visual Basic? Thank you for the explanation. Also, are you saying that the Xml that the query produces will change at some point & hence become unreadable by the dataset?

John Saunders Over a year ago

Using statements are like Try ... Finally x.Dispose() End Try. They ensure that Dispose is called on the value in the expression in the clause.

Brandon Lumsden Over a year ago

Oh okay, so Dispose() is doing the work of the reader.Close() & conn.Close() that was in my code initially. The using statements call Dispose() on it's own without me having to call the close method. At least this is what i'm gathering from your post. Very informative!

John Saunders Over a year ago

See IDisposable interface.

Community · Accepted Answer · 2017-05-23 10:33:47Z

1

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?

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Jun 2, 2015 at 22:23

Cadburry

1,86410 silver badges22 bronze badges

2 Comments

Brandon Lumsden Over a year ago

So I would do : SqlXml xmlData = reader.GetSqlXml(0); Console.WriteLine(xmlData); or would it be something different?

Brandon Lumsden Over a year ago

I tried using all suggestions in that link and nothing has worked. I keep getting an error on this line SqlXml xmldata = reader.GetSqlXml(0); Unable to cast object of type 'System.Data.SqlTypes.SqlString' to type 'System.Data.SqlClient.SqlCachedBuffer'.

Brandon Lumsden · Accepted Answer · 2015-06-03 20:23:28Z

1

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.

answered Jun 3, 2015 at 20:23

Brandon Lumsden

791 gold badge2 silver badges10 bronze badges

1 Comment

John Saunders Over a year ago

This is not a good solution in the general case. DataSet.ReadXml cannot read every XML document.

Collectives™ on Stack Overflow

Format XML Returned from SQL Server

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related