Fetch XML from database & merge them

Question

I have a table in which a column with xml datatype has many xmls in following format.

 <Hash HashValue = "9738839398923">
     <Element1>
            <childelement1 attribute1 = "..." attribute2 = "...">
            <childelement2 attribute1 = "..." attribute2 = "...">
            <childelement3 attribute1 = "..." attribute2 = "...">
    </Element1>
    <Element2>
           ......
           ......
    </Element2>
</Hash>

Now, i want to fetch all this rows from table & merge them. I have done the fetching part & stored it in a Datatable.

For merging, i went to different solutions on this site but i couldn't get the satisfying result.

Expected Output Format:

    <Hash HashValue = "4972904980">
         .......
         ......
     </Hash>
    <Hash HashValue = "4534543">
         .......
         ......
     </Hash>
   <Hash HashValue = "536456456456">
         .......
         ......
     </Hash>

The closest I got so far is :

<Hash HashValue = "4972904980">
      <Hash HashValue = "4534543">
         .......
         ......
     </Hash>
  </Hash>

Code for above output:

            FileStream file = File.Open(fakePath, FileMode.Create);
            XElement xFileRoot = XElement.Parse(dt.Rows[0]["ColumnName"].ToString());
            XElement xFileChild = XElement.Parse(dt.Rows[1]["ColumnName"].ToString());
            xFileRoot.Add(xFileChild);
            xFileRoot.Save(file); 

Above code clearly treat the second xml as child of first one which is clearly not my intention.

How to achieve my expected output?

Answer 1

XML only allows a single root element. So you need to create that element explicitly - it can't be one of your existing elements.

One way of doing it:

        XDocument xDocument = new XDocument();
        var root = new XElement("root");
        xDocument.Add(root);

        root.Add(XElement.Parse("<entry hash='1'/>"));
        root.Add(XElement.Parse("<entry hash='2'/>"));

        var output = string.Join("\r\n", root.Elements());

Instead of the two root.Add statements, you should loop over your records.

It's not the most efficient way to create the XML - but then neither is reading the data into a DataTable - so if it is fast enough for you, then it's fine.

Answer 2

For XML to be valid, there must be 1 single root element.

You are adding the next "element" as a child of the root. This makes no sense if the root and child element are the same element.

I suggest you simply make a dummy root element called? rootElement?

XmlElement xFileRoot = doc.CreateElement("rootElement");

and then

foreach(var row in dt.Rows)
{
   XElement xFileChild = XElement.Parse(row["ColumnName"].ToString());
   xFileRoot.Add(xFileChild);
}

and for all intents ignore that root element exists.

Your desired outcome in other words is not valid XML.