VB解析含子元素的大XML：捕获所有属性值并导入SQL

需要读取XML文件，捕获所有元素与属性值（包括Default、Deduct这类未知属性），完成转换计算后上传至SQL数据库。目前能获取<Cot>节点的PList、Name、Desc属性值，但无法正确捕获<Color>子节点的Code、Price等数据；期望输出格式为PList、Name、Desc、Code、Price，其中前三者在遇到下一个<Cot>节点前保持不变。由于XML文件体积较大，计划用XmlReader替代XDocument，现附上两类XML样本及尝试过的XDocument代码，寻求能将所有数据存入变量、适配大XML的实现示例。

样本XML1 #

<?xml version="1.0" encoding="iso-8859-1" ?>
<CoatSchedules>
<Cot PList="02" Name="CDC" Desc="PAR/PAV/EX3/RCH/EXP">
<Color Code="ARC" Price="39.58"/>
<Color Code="BAR" Price="39.58" Default="50.00"/>
<Color Code="BEP" Price="58.54"/>
<Color Code="BEX" Price="51.54"/>
</Cot>
<Cot PList="0A" Name="E6C" Desc="PAR/PAV" Deduct="2.00">
<Color Code="BPA" Price="24.00"/>
<Color Code="BPV" Price="24.00"/>
<Color Code="COT" Price="0.00"/>
<Color Code="PAR" Price="24.00"/>
<Color Code="PAV" Price="24.00"/>
<Color Code="UTP" Price="25.00"/>
<Color Code="UTV" Price="20.00"/>
<Color Code="UV" Price="6.72"/>
</Cot>
</CoatSchedules>

尝试的XDocument代码 #

For Each element As XElement In xd.Root.Elements("Cot")
            Console.WriteLine("PList: {0}; Name: {1}; Desc:{2}; Code: {3}; Price: {4}", CStr(element.Element("PList").Value), CStr(element.Element("Name")), CStr(element.Element("Desc")), CStr(element.Element("Code")), CStr(element.Element("Price")))
            Dim PListValue = element.Attribute("PList").Value
            Dim NameValue = element.Attribute("Name").Value
            Dim DescriptionValue = element.Attribute("Desc").Value
            Dim ColorCodeValue
            Dim PriceValue

            Console.WriteLine("PList: {0}; Name: {1}; Desc:{2};", PListValue, NameValue, DescriptionValue)

            For Each child As XElement In element.Elements("Color")
                Dim ColorCodeValue = child.Attribute("Code").Value
                Dim PriceValue = child.Attribute("Price").Value
                Console.WriteLine(" Code: {0}; Price: {1}", ColorCodeValue, PriceValue)
            Next
        Next

大XML样本 #

<?xml version="1.0" encoding="iso-8859-1" ?>
<Styles>
<Material PList="02" Code="B53">
<Style Name="ARRAY *" Fin="S" Sph="92.70" POW="012" PRS="001" BCV="002"
 COL="CLR" TNT="002" COT="R8C" EDG="602" FRM="001"/>
<Style Name="ARRAY 2 *" Fin="S" Sph="92.70" POW="012" PRS="001" BCV="002"
 COL="CLR" TNT="002" COT="R8C" EDG="602" FRM="001"/>
<Style Name="ARRAY 2 W *" Fin="S" Sph="92.70" POW="012" PRS="001" BCV="002"
 COL="CLR" TNT="002" COT="R8C" EDG="602" FRM="001"/>
<Style Name="ARRAY W *" Fin="S" Sph="92.70" POW="012" PRS="001" BCV="002"
 COL="CLR" TNT="002" COT="R8C" EDG="602" FRM="001"/>
</Material>
<Material PList="02" Code="B67">
<Style Name="ARRAY *" Fin="S" Sph="92.70" POW="013" PRS="001" BCV="002"
 COL="CLR" TNT="002" COT="R8C" EDG="604" FRM="001"/>
<Style Name="ARRAY 2 *" Fin="S" Sph="92.70" POW="013" PRS="001" BCV="002"
 COL="CLR" TNT="002" COT="R8C" EDG="604" FRM="001"/>
<Style Name="ARRAY 2 W *" Fin="S" Sph="92.70" POW="013" PRS="001" BCV="002"
 COL="CLR" TNT="002" COT="R8C" EDG="604" FRM="001"/>
<Style Name="ARRAY W *" Fin="S" Sph="92.70" POW="013" PRS="001" BCV="002"
 COL="CLR" TNT="002" COT="R8C" EDG="604" FRM="001"/>
</Material>
...
</Material>
</Styles>

XmlReader采用流式读取，不会一次性加载整个XML到内存，完美适配大文件场景。以下是针对两类XML的实现示例，同时支持捕获未知属性。

一、针对样本XML1的XmlReader实现（VB.NET） #

首先定义类存储单条数据（含未知属性）：

Public Class CoatData
    Public Property PList As String
    Public Property Name As String
    Public Property Desc As String
    Public Property Code As String
    Public Property Price As Decimal
    ' 存储未知属性，键为属性名，值为属性值
    Public Property ExtraAttributes As New Dictionary(Of String, String)
End Class

读取逻辑：

Dim coatDataList As New List(Of CoatData)
Dim currentPList As String = String.Empty
Dim currentName As String = String.Empty
Dim currentDesc As String = String.Empty
Dim currentCotExtraAttrs As New Dictionary(Of String, String)

Using reader As XmlReader = XmlReader.Create("你的XML文件路径")
    While reader.Read()
        ' 处理<Cot>节点
        If reader.NodeType = XmlNodeType.Element AndAlso reader.Name = "Cot" Then
            ' 获取已知属性
            currentPList = reader.GetAttribute("PList")
            currentName = reader.GetAttribute("Name")
            currentDesc = reader.GetAttribute("Desc")
            ' 捕获<Cot>节点的未知属性
            currentCotExtraAttrs.Clear()
            For i As Integer = 0 To reader.AttributeCount - 1
                reader.MoveToAttribute(i)
                Dim attrName = reader.Name
                If attrName <> "PList" AndAlso attrName <> "Name" AndAlso attrName <> "Desc" Then
                    currentCotExtraAttrs.Add(attrName, reader.Value)
                End If
            Next
            reader.MoveToElement()
        End If

        ' 处理<Color>节点
        If reader.NodeType = XmlNodeType.Element AndAlso reader.Name = "Color" Then
            Dim data As New CoatData With {
                .PList = currentPList,
                .Name = currentName,
                .Desc = currentDesc,
                .Code = reader.GetAttribute("Code"),
                .Price = Convert.ToDecimal(reader.GetAttribute("Price"))
            }
            ' 复制<Cot>节点的未知属性
            For Each kvp In currentCotExtraAttrs
                data.ExtraAttributes.Add(kvp.Key, kvp.Value)
            Next
            ' 捕获<Color>节点的未知属性
            For i As Integer = 0 To reader.AttributeCount - 1
                reader.MoveToAttribute(i)
                Dim attrName = reader.Name
                If attrName <> "Code" AndAlso attrName <> "Price" Then
                    data.ExtraAttributes.Add(attrName, reader.Value)
                End If
            Next
            coatDataList.Add(data)
        End If
    End While
End Using

' 示例：输出数据
For Each data In coatDataList
    Console.WriteLine($"PList: {data.PList}, Name: {data.Name}, Desc: {data.Desc}, Code: {data.Code}, Price: {data.Price}")
    If data.ExtraAttributes.Count > 0 Then
        Console.WriteLine("额外属性：")
        For Each kvp In data.ExtraAttributes
            Console.WriteLine($"  {kvp.Key}: {kvp.Value}")
        Next
    End If
    Console.WriteLine("---")
Next

二、针对大XML样本的XmlReader实现（VB.NET） #

定义数据类：

Public Class StyleData
    Public Property PList As String
    Public Property MaterialCode As String
    ' 存储Style节点的所有属性（含未知属性）
    Public Property StyleAttributes As New Dictionary(Of String, String)
End Class

读取逻辑：

Dim styleDataList As New List(Of StyleData)
Dim currentPList As String = String.Empty
Dim currentMaterialCode As String = String.Empty

Using reader As XmlReader = XmlReader.Create("大XML文件路径")
    While reader.Read()
        ' 处理<Material>节点
        If reader.NodeType = XmlNodeType.Element AndAlso reader.Name = "Material" Then
            currentPList = reader.GetAttribute("PList")
            currentMaterialCode = reader.GetAttribute("Code")
        End If

        ' 处理<Style>节点
        If reader.NodeType = XmlNodeType.Element AndAlso reader.Name = "Style" Then
            Dim data As New StyleData With {
                .PList = currentPList,
                .MaterialCode = currentMaterialCode
            }
            ' 捕获Style节点的所有属性
            For i As Integer = 0 To reader.AttributeCount - 1
                reader.MoveToAttribute(i)
                data.StyleAttributes.Add(reader.Name, reader.Value)
            Next
            styleDataList.Add(data)
        End If
    End While
End Using

三、SQL上传建议 #

1. 批量插入：使用SqlBulkCopy实现批量上传，避免单条插入的性能损耗，适合大量数据场景。
2. 数据校验：插入前对数值型数据（如Price）做合法性验证，避免SQL插入错误。
3. 事务处理：开启数据库事务，若上传过程中出现异常可回滚，保证数据一致性。

内容的提问来源于stack exchange，提问作者Ledz3p