使用C#（或其他方法）将XML转换为SQL Server表

Question

使用C#（或其他方法）将XML转换为SQL Server表

4

我有大约 10,000 个 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>

通过上面的例子，我应该期望创建一个包含以下列名的表：color, height, weight, price, distance（因为XML #2具有distance），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.J.

1

获取文件以遵循特定的XML模式定义。标签为空或NULL是可以的，但标签存在非常重要。这样处理文件会更加容易。 - abhi

1

一个可能的解决方案是遍历每个 XML 文件并提取所有独特字段，然后在数据库中创建一个包含所有提取的独特字段的表。这样您就可以知道拥有哪些字段，以便稍后考虑规范化表格。 - keenthinker

1

@Hituptony 我正要发布同样的链接。 - abhi

1

你可以获取一个包含所有可能标签的示例文件。然后，Visual Studio将为您创建XSD。市场上还有其他更擅长此类工作的工具。Altova XML SPY和Liquid是其中之一。在2011年，我正在进行非常类似的活动。 - abhi

@MikaelEriksson 确认一个XML等于一行。但是，在进行查询时，xquery要求您指定路径。首先，我可能不知道每个XML中的节点可能呈现什么，其次，如上例所示，在XML＃1中，<color>是具有自身值的节点，但在XML＃4中，<color>只是父节点。我将如何基于这些编写动态查询？ - C.J.

显示剩余8条评论

2个回答

1

你可以使用xQuery、一个临时表和动态透视来在TSQL中实现此操作。

临时表：

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 中进行。

- Mikael Eriksson

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- keenthinker · Accepted Answer

遍历所有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 表的列。稍加改进，您还可以标记父节点和子节点。这样可以帮助您进行规范化。