Converting XML to SQL Server Table using C# (or any other method)

c#, sql, sql-server, xml

Solution

One possibility to iterate over all xml files and get all unique tags could use LINQ2XML, the HashSet class and could look like this:

try
{
    // add as many elements you want, they will appear only once!
    HashSet<String> uniqueTags = new HashSet<String>();
    // recursive helper delegate
    Action<XElement> addSubElements = null;
    addSubElements = (xmlElement) =>
    {
        // add the element name and 
        uniqueTags.Add(xmlElement.Name.ToString());
        // if the given element has some subelements
        foreach (var element in xmlElement.Elements())
        {
            // add them too
            addSubElements(element);
        }
    };

    // load all xml files
    var xmls = Directory.GetFiles("d:\\temp\\xml\\", "*.xml");
    foreach (var xml in xmls)
    {
        var xmlDocument = XDocument.Load(xml);
        // and take their tags
        addSubElements(xmlDocument.Root);
    }
    // list tags
    foreach (var tag in uniqueTags)
    {
        Console.WriteLine(tag);
    }
}
catch (Exception exception)
{
    Console.WriteLine(exception.Message);
}

Now you have the columns for the basic SQL table. With little enhancing, you could also mark the parent and the sub nodes. This could help you for the normalization.

Problem

I have about 10,000 XML files where I need to convert them into SQL table. However, here are the problems, each XML files has some variations between each other thus it is almost impossible for me to specify the element name. For example: ``` //XML #1 <color>Blue</color> <height>14.5</height> <weight>150</weight> <price>56.78</price> //XML #2 <color>Red</color> <distance>98.7</distance> <height>15.5</height> <price>56.78</price> //XML #3: Some of the elements have no value <color /> <height>14.5</height> <price>78.11</price> //XML #4: Elements has parent/child <color> <bodyColor>Blue</bodyColor> <frontColor>Yellow</frontColor> <backColor>White</backColor> </color> <height>14.5</height> <weight>150</weight> <price>56.78</price> ``` With the example above, I should expect a table created with `columns` name: `color, height, weight, price, distance` (Because XML #2 has distance), `bodyColor, frontColor, backColor`. Expected output: ``` XML# color height weight price distance bodyColor frontColor backColor 1 Blue 14.5 150 56.78 NULL NULL NULL NULL 2 Red 15.5 NULL 56.78 98.7 NULL NULL NULL 3 NULL 14.5 NULL 78.11 NULL NULL NULL NULL 4 NULL 14.5 150 56.78 NULL Blue Yellow White ``` In this case, NULL or empty value are acceptable. These are just examples, there are at least 500 elements in each XML file. Also, even though I mentioned C# here, if anyone can suggest a better way of doing so, please let me know.

Original source