Convert dynamic DataTable to XML

Question

I have a stored procedure that returns variable data from an EAV database and want to export the data as an XML.

DataTable columns:

group varchar
attribute varchar
value varchar

Datatable Example:

group       attribute      value
General     Name           Big Bird
BodyType    Color          Yellow
BodyType    Height         200
General     Age            5

Desired XML:

<RootTag>
   <General>
     <Name>Big Bird</Name>
     <Age>5</Age>
   </General
   <BodyType>
     <Color>Yellow</Color>
     <Height>200</Height>
   </BodyType>
</RootTag>

If I do a DataTable.WriteXml I get:

<Item>
  <group>General</group>
  <attribute>Name</attribute>
  <value>Big Bird</value>
</Item>
<Item>
  <group>BodyType</group>
  <attribute>Color</attribute>
  <value>Yellow</value>
</Item>
...

I am currently building a type dynamically at run-time with reflection and serializing with that type. I'm thinking there has to be a better way.

EDIT: DataTable.WriteXml sample

public static string BuildFullXML(DataTable OrderData, string OutPath)
{
    if (!Directory.Exists(OutPath)) { Directory.CreateDirectory(OutPath); }
    OutPath += DateTime.Now.Ticks + ".xml";

    OrderData.TableName = "Item";

    using (TextWriter TW = File.CreateText(OutPath))
    {
        OrderData.WriteXml(TW);
        TW.Close();
    }

    return path;
}

score 1 · Accepted Answer · answered Nov 15 '16 at 21:48

1

Try using the DataTable.WriteXml(writer) method which provides a way to write either data only, or both data and schema from a DataTable into an Xml document.

Maybe this code sample will help you understand how to use this method:

    string xmlString = string.Empty;
    using (TextWriter writer = new StringWriter())
    {
            table.WriteXml(writer);
            xml = writer.ToString();
    }

Hope this helped you! :)

answered Nov 15 '16 at 21:48

DannyDSB

145
2
12

You might have missed the part at the end where I already tried an DataTable.WriteXML and the output isn't in the desired format. Please expand your answer to include information about how to use the WriteXML function to alter the output to match the desired output example. – 182764125216 Nov 17 '16 at 17:17
Can you write a code block to let me check where you've written something wrong? Maybe I will see the problem. Thanks! – DannyDSB Nov 17 '16 at 17:37
I added the method. – 182764125216 Nov 17 '16 at 19:56
1

See this link because it might help you --> [link](https://msdn.microsoft.com/en-us/library/system.data.datatable.writexml(v=vs.110).aspx) But I think that the problem is here `TextWriter TW = File.CreateText(OutPath)` – DannyDSB Nov 17 '16 at 20:04

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

Your data structure is modeled in a weird way. Each line represents a record in a normal database so the XML is going to assume that each line is its own item. I would think about how you are modeling the data.

If you HAVE to model it this way you should serialize your data table to an object and store a list of the that object. That way you can tell the serialize how to make your XML document.

Look into using attributes to define your XML document. Like in this link How to add attributes for C# XML Serialization

edited May 23 '17 at 12:24

Community

1
1

answered Nov 15 '16 at 21:39

Sam Marion

690
1
4
17

The data is structured that way because it is variable from an EAV database. I don't know what attributes exist for each item and the information people want changes over time. Doing it this way has saved a bunch of time in code changes. When different data is needed a new attribute is created in the EAV structure and automatically output in the XML and used by various XML recipients. Without any changes to the code. – 182764125216 Nov 15 '16 at 21:47
My apologies, I somehow missed the part where you mentioned it was EAV – Sam Marion Nov 15 '16 at 21:52

Convert dynamic DataTable to XML

2 Answers2