C# xmlwriter sql server

Question

From SQL Server 2008, used for xml auto, root('ROOT'), type, elements xsinil to get this output:

<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<node>
  <nodeid>28</nodeid>
  <account_no>0</account_no>
  <address1>15 CANCUN CT</address1>
  <serial_no>112199543</serial_no>
  <x_lat>25.95513358000</x_lat>
  <y_lon>-97.49027147000</y_lon>
  <alarm>
    <alarmid>Outage</alarmid>
    <alarmtime>2012-08-03T16:27:15.830</alarmtime>
  </alarm>
 <alarm>
   <alarmid>Restore</alarmid>
   <alarmtime>2012-08-06T09:41:53.313</alarmtime>
 </alarm>
</node>
</ROOT>

I'm trying to get the same result as above from SQL Server from C# with a XmlWriter.

Here is my code:

private void writeXmlToBrowser(DataTable xml)
{
    XmlWriterSettings settings = new XmlWriterSettings();
    settings.OmitXmlDeclaration = true;
    settings.Indent = true;

    using (XmlWriter writer = XmlWriter.Create(Response.OutputStream, settings))
    {
        if (xml != null)
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("ROOT", "http://www.w3.org/2001/XMLSchema-instance");
            foreach (DataRow row in xml.Rows)
            {
                writer.WriteStartElement("node");

                foreach (DataColumn col in xml.Columns)
                {
                    writer.WriteElementString(col.ColumnName, row[col].ToString().Trim());
                }

                writer.WriteEndElement();
            }

            writer.WriteEndElement();
            writer.WriteEndDocument();
            writer.Flush();
        }
        else
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("ROOT", "http://www.w3.org/2001/XMLSchema-instance");
            writer.WriteEndElement();
            writer.WriteEndDocument();
            writer.Flush();
        }
    }
}

and the output of this code is this (two node elements) I only want it as the same as formatted SQL Server output.

<ROOT xmlns="http://www.w3.org/2001/XMLSchema-instance">
 <node>
  <nodeid>28</nodeid>
  <account_no>0</account_no>
  <address1>15 CANCUN CT</address1>
  <serial_no>112199543</serial_no>
  <alarmid>Outage</alarmid>
  <alarmtime>8/3/2012 4:27:15 PM</alarmtime>
  <x_lat>25.95513358000</x_lat>
  <y_lon>-97.49027147000</y_lon>
 </node>
 <node>
  <nodeid>28</nodeid>
  <account_no>0</account_no>
  <address1>15 CANCUN CT</address1>
  <serial_no>112199543</serial_no>
  <alarmid>Restore</alarmid>
  <alarmtime>8/6/2012 9:41:53 AM</alarmtime>
  <x_lat>25.95513358000</x_lat>
  <y_lon>-97.49027147000</y_lon>
 </node>
</ROOT>

UPDATE! Here is the code i used instead. shorter and simpler.

protected void Page_Load(object sender, EventArgs e)
{
    X();
    writeXml();
}

private void X()
{
    x = new XmlDocument();
    using (SqlConnection con = new SqlConnection(cn))
    {
        using (SqlCommand cmd = new SqlCommand(sqlstring, con))
        {
            con.Open();
            using (XmlReader reader = cmd.ExecuteXmlReader())
            {
                x.Load(reader);
            }
        }
    }
}

private void writeXml()
{
    XmlWriterSettings settings = new XmlWriterSettings();
    settings.Indent = true;
    settings.OmitXmlDeclaration = true;
    using (XmlWriter xr = XmlWriter.Create(Response.OutputStream, settings))
    {
        x.WriteContentTo(xr);
        xr.Flush();
    }
}

Barracoder · Accepted Answer · 2012-08-06 15:15:55Z

2

I find it a lot easier to serialize objects rather than trying to build this up in code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Serialization;
using System.Xml.Linq;

namespace SO.XmlSqlServer
{
    class Program
    {
        static void Main(string[] args)
        {
            var node = new SqlOutput
            {
                NodeId = 28,
                AccountNumber = 0,
                AddressLine1 = "15 CANCUN ST",
                SerialNo = 112156544,
                XLatitude = 25.23456354,
                YLongitude = -97.54435453,
                Alarms = new List<Alarm>(new[]
                {
                    new Alarm
                    {
                        AlarmId="Outage",
                        AlarmTime=DateTime.Now
                    },
                    new Alarm
                    {
                        AlarmId="Restore",
                        AlarmTime=DateTime.Now
                    }
                })
            };

            XmlSerializer ser = new XmlSerializer(typeof(SqlOutput));
            XDocument doc = new XDocument();
            using (var writer = doc.CreateWriter())
            {
                ser.Serialize(writer, node);
            }
            Console.WriteLine(doc.ToString());
            Console.ReadLine();
        }
    }

    [XmlRoot("node")]
    public class SqlOutput
    {
        [XmlElement("nodeid")]
        public int NodeId { get; set; }

        [XmlElement("account_no")]
        public int AccountNumber { get; set; }

        [XmlElement("address1")]
        public string AddressLine1 { get; set; }

        [XmlElement("serial_no")]
        public int SerialNo { get; set; }

        [XmlElement("x_lat")]
        public double XLatitude { get; set; }

        [XmlElement("y_lon")]
        public double YLongitude { get; set; }

        [XmlElement("alarm")]
        public List<Alarm> Alarms { get; set; }
    }

    public class Alarm
    {
        public string AlarmId { get; set; }
        public DateTime AlarmTime { get; set; }
    }

}

answered Aug 6, 2012 at 15:15

Barracoder

3,7842 gold badges30 silver badges31 bronze badges

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

3 Comments

Fonzy Over a year ago

i'm displaying the output to a browser window not the console. plus there are two entries in the db for node 28 (hence the two nodes in the second xml)

Barracoder Over a year ago

Just change Console.WriteLine() to HttpRequest.Write() or whatever method you're using to wtite the code.

Fonzy Over a year ago

I didn't go this route but it is a good way though, I just returned the xml document from sql using the -for xml auto.. and returned it to a C# XmlDocument object and wrote it to the XmlWriter through Response.OutputStream.

Collectives™ on Stack Overflow

C# xmlwriter sql server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related