Convert XML table in SQL Server using C # (or any other method)

Question

I have about 10,000 XML files where I need to convert them to an SQL table.

However, here are the problems, each XML file has some differences, so it is almost impossible for me to specify the name of the element. For instance:

//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>

In the example above, I should expect a table created by a columnsname: color, height, weight, price, distance(as XML # 2 has a distance) bodyColor, frontColor, backColor.

Expected Result:

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 an empty value is allowed.

These are just examples; there are at least 500 elements in each XML file. Also, although I mentioned C # here, if someone can suggest a better way to do this, let me know.

Answer 1

xml LINQ2XML, HashSet :

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);
}

SQL. . .

Answer 2

TSQL, xQuery, .

:

create table dbo.XMLStage
(
  ID uniqueidentifier not null,
  Name nvarchar(128) not null,
  Value nvarchar(max) not null,
  primary key (Name, ID)
);

ID , Name node Value node.

:

create procedure dbo.LoadXML
  @XML xml
as

declare @ID uniqueidentifier;
set @ID = newid();

insert into dbo.XMLStage(ID, Name, Value)
select @ID,
       T.X.value('local-name(.)', 'nvarchar(128)'),
       T.X.value('text()[1]', 'nvarchar(max)')
from @XML.nodes('//*[text()]') as T(X);

//*[text()]

, :

declare @Cols nvarchar(max);
declare @SQL nvarchar(max);

set @Cols = (
            select distinct ',' + quotename(X.Name)
            from dbo.XMLStage as X
            for xml path(''), type
            ).value('substring(text()[1], 2)', 'nvarchar(max)');

set @SQL = '
select '+@Cols+'
from dbo.XMLStage
pivot (max(Value) for Name in ('+@Cols+')) as P';

exec sp_executesql @SQL;

SQL Fiddle