使用C#(或任何其他方法)将XML转换为SQL Server表

本文关键字：转换 XML SQL Server 任何 方法 其他 使用 | 更新日期: 2023-09-27 17:59:50

我有大约10000个XML文件，需要将它们转换为SQL表。

然而，问题是，每个XML文件之间都有一些变化，因此我几乎不可能指定元素名称。例如：

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

对于上面的示例，我应该期望使用columns名称创建一个表：color, height, weight, price, distance（因为XML#2有距离），bodyColor, frontColor, backColor。

预期输出：

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

在这种情况下，NULL或空值是可以接受的。

这些只是示例，每个XML文件中至少有500个元素。此外，尽管我在这里提到了C#，但如果有人能提出更好的方法，请告诉我。

使用C#(或任何其他方法)将XML转换为SQL Server表

迭代所有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表的列。只需少量增强，就可以标记父节点和子节点。这可以帮助您实现正常化。

您可以在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保存节点名称，Value保存节点值。

填充暂存表的存储过程：

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 中试用它

相关文章：