Retrieve XML column from DataBase with lines C#

Question

I run query against DB which returns me 1 row 2 columns. Each column has XML(script) inside which I need to retrieve into (ideally string) and then execute against DB. I need the retrieved string to have lines and formatting as if i would copy if from SQL management studio.

Part of XMLresult from SQL management studio I need this format. replacement: XXXXXX

<message>
  <header>
    <meta version="02" type="OrMessageUdalost_Int" />
    <sender name="XXXXXX" appid="XXXXXX" netid="XXXXXX" />
    <receiver name="XXXXXX" appid="XXXXXX" netid="XXXXXX" />
    <timestamp date="XXXXXX" time="10:11:00" gmt="+XXXXXX" />
    <options priority="4" props="XXXXXX" limit="100" />
  </header>
  <content>
    <q1:orMessageUdalost_Int xmlns:q1="XXXXXX" verze="v_2.11">
      <hlavicka>
        <idDatovaVeta>XXXXXX</idDatovaVeta>
        <datumVytvoreni>XXXXXX</datumVytvoreni>
        <ciselnikAktualizace>false</ciselnikAktualizace>
        <ostrovniRezim>false</ostrovniRezim>
        <puvodceDV>
          <id>XXXXXX</id>
          <kod>XXXXXX</kod>
        </puvodceDV>
        <idPuvodniDV>XXXXXX</idPuvodniDV>
        <puvodniDatumVytvoreni>XXXXXX</puvodniDatumVytvoreni>
        <stavUdalosti>XXXXXX</stavUdalosti>
        <stavSlozky>
          <slozka>
            <id>XXXXXX</id>
            <kod>XXXXXX</kod>
          </slozka>
          <isStav>XXXXXX</isStav>
        </stavSlozky>
      </hlavicka>
      <teloFull>

Thanks for any advice.

score 0 · Accepted Answer · answered Jun 17 '21 at 08:36

So the key is to get the proper indentation?

In that case, I would recommend taking a look at the following SO thread:

Format XML string to print friendly XML string

The key is that you need indentation, which can be achieved through the XmlTextWrite.Formatting = Formatting.Indented option.

jdweng · Answer 2 · 2021-06-17T10:45:21.213

Use XML serializer. See code below

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

namespace ConsoleApplication193
{
    class Program
    {
        const string INPUT_FILENAME = @"c:\temp\test.xml";
        const string OUTPUT_FILENAME = @"c:\temp\test1.xml";
        static void Main(string[] args)
        {
            string xml = File.ReadAllText(INPUT_FILENAME);
            StringReader sReader = new StringReader(xml);

            XmlReader reader = XmlReader.Create(sReader);
            XmlSerializer serializer = new XmlSerializer(typeof(Message));
            Message message = (Message)serializer.Deserialize(reader);

            //write
            XmlSerializerNamespaces names = new XmlSerializerNamespaces();
            names.Add("q1", "XXXXXX");

            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;
            MemoryStream stream = new MemoryStream();
            XmlWriter writer = XmlWriter.Create(stream);
            serializer.Serialize(writer,message,names);
            byte[] buffer = new byte[stream.Length];
            stream.Position = 0;
            stream.Read(buffer, 0, (int)stream.Length);
            string output = Encoding.UTF8.GetString(buffer);
          
        }
    }
    [XmlRoot("message")]
    public class Message
    {
        public Header header { get; set; }
        public Content content { get; set; }
    }
    public class Header
    {
        public Meta meta { get; set; }
        public Sender sender { get; set; }
        public Receiver receiver { get; set; }
        public Timestamp timestamp { get; set; }
        public Options options { get; set; }
    }
    public class Meta
    {
        [XmlAttribute("version")]
        public string version { get; set; }
        [XmlAttribute("type")]
        public string type { get; set; }
    }
    public class Sender
    {
        [XmlAttribute()]
        public string name { get; set; }
        [XmlAttribute()]
        public string appid { get; set; }
        [XmlAttribute()]
        public string netid { get; set; }
    }
    public class Receiver
    {
        [XmlAttribute()]
        public string name { get; set; }
        [XmlAttribute()]
        public string appid { get; set; }
        [XmlAttribute()]
        public string netid { get; set; }
    }
    public class Timestamp
    {
        [XmlAttribute()]
        public string date { get; set; }
        [XmlAttribute()]
        public DateTime  timespan { get; set; }
        [XmlAttribute()]
        public string gmt { get; set; }
    }
    public class Options
    {
        [XmlAttribute()]
        public int priority { get; set; }
        [XmlAttribute()]
        public string props { get; set; }
        [XmlAttribute()]
        public int limit { get; set; }
    }
    public class Content
    {
        [XmlElement(Namespace = "XXXXXX")]
        public OrMessageUdalost_Int orMessageUdalost_Int { get; set; }
    }
   public class OrMessageUdalost_Int
    {
        [XmlAttribute()]
        public string verze { get; set; }
        [XmlElement(Namespace = "")]
        public Hlavicka hlavicka { get; set; }
    }
    public class Hlavicka
    {
        public string idDatovaVeta { get; set; }
        public string datumVytvoreni { get; set; }
        public string ciselnikAktualizace { get; set; }
        public Boolean  ostrovniRezim { get; set; }
        public PuvodceDV puvodceDV { get; set; }
        public string idPuvodniDV { get; set; }
        public string puvodniDatumVytvoreni { get; set; }
        public string stavUdalosti { get; set; }
        [XmlArray("stavSlozky")]
        [XmlArrayItem("slozka")]
        public List<Slozka> slozka { get; set; }

        public TeloFull teloFull { get; set; }
    }
    public class PuvodceDV
    {
        public string id { get; set; }
        public string kod { get; set; }
    }
    public class Slozka
    {
        public string id { get; set; }
        public string kod { get; set; }
    }
    public class TeloFull
    {
    }
  
}

Retrieve XML column from DataBase with lines C#

2 Answers2