当 XML 具有具有相同节点名称的节点时，将 XML 数据插入 SQL 数据库

Question

我是 C# 的新手，我正在用 C# 开发一个代码，它从一个具有 XML 格式数据的变量中读取数据。

有些节点在重复，我的代码只读取重复节点的第一条记录。

我的 XML 示例：

<Stores>

    <Products>

        <Size>Small</Size>

        <ShortName>Coke</ShortName>

        <Id>000001</Id>

        <Description>Drinks</Description>

        <OptionalItemIds>100001</OptionalItemIds>

        <OptionalItemIds>100002</OptionalItemIds>

        <OptionalItemIds>100003</OptionalItemIds>

        <OptionalItemIds>100004</OptionalItemIds>

        <OptionalItemIds>100005</OptionalItemIds>

        <Calories>0</Calories>

        <Name>Diet Coke</Name>

    </Products>

</Stores>

我试过的一段代码：

var ProductList = doc.Root.Elements("Products").Select(element => new JMMEntity

            {

                Size = (element.HasElements == true && element.Element("Size") != null) ? element.Element("Size").Value : String.Empty,

                ShortName = (element.HasElements == true && element.Element("ShortName") != null) ? element.Element("ShortName").Value : String.Empty,

                Id = (element.HasElements == true && element.Element("Id") != null) ? element.Element("Id").Value : String.Empty,

                Description = (element.HasElements == true && element.Element("Description") != null) ? element.Element("Description").Value : String.Empty,

                OptionalItemIds = (element.HasElements == true && element.Element("OptionalItemIds") != null) ? element.Element("OptionalItemIds").Value : String.Empty,

                Calories = (element.HasElements == true && element.Element("Calories") != null) ? element.Element("Calories").Value : String.Empty,

                Name = (element.HasElements == true && element.Element("Name") != null) ? element.Element("Name").Value : String.Empty

            }).ToList();

Answer 1

您需要单独使用CROSS APPLY和获取枚举<OptionalItemIds>- 尝试这样的事情：INSERT INTO Products(Size, ShortName, Id, Description, OptionalItemIds, Calories, Name)&nbsp; &nbsp; SELECT&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; tbl.Stores.value('Size[1]', 'nvarchar(20)') AS Size,&nbsp; &nbsp; &nbsp; &nbsp; tbl.Stores.value('ShortName[1]', 'nvarchar(20)') AS ShortName,&nbsp; &nbsp; &nbsp; &nbsp; tbl.Stores.value('Id[1]', 'nvarchar(250)') AS Id,&nbsp; &nbsp; &nbsp; &nbsp; tbl.Stores.value('Description[1]', 'nvarchar(20)') AS Description,&nbsp; &nbsp; &nbsp; &nbsp; -- tbl.Stores.value('OptionalItemIds.[1]', 'nvarchar(250)') AS OptionalItemIds,&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; tbl2.ItemIds.value('.', 'int') AS OptionalItemId,&nbsp; &nbsp; &nbsp; &nbsp; tbl.Stores.value('Calories[1]', 'nvarchar(20)') AS Calories,&nbsp; &nbsp; &nbsp; &nbsp; tbl.Stores.value('Name[1]', 'nvarchar(20)') AS Name&nbsp; &nbsp; FROM&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; @XmlProduct.nodes('/Stores/Products') AS tbl(Stores)&nbsp; &nbsp; -- get the list of "OptionalItemIds" separately, cross apply to produce rows to be inserted&nbsp; &nbsp; CROSS APPLY&nbsp; &nbsp; &nbsp; &nbsp; @XmlProduct.nodes('/Stores/Products/OptionalItemIds') AS tbl2(ItemIds);

Answer 2

这段代码对我有用，&nbsp; &nbsp; INSERT INTO Products(Size, ShortName, Id, Description, OptionalItemIds, Calories, Name)SELECT&nbsp;&nbsp;&nbsp; &nbsp; tbl.Stores.value('Size[1]', 'nvarchar(20)') AS Size,&nbsp; &nbsp; tbl.Stores.value('ShortName[1]', 'nvarchar(20)') AS ShortName,&nbsp; &nbsp; tbl.Stores.value('Id[1]', 'nvarchar(250)') AS Id,&nbsp; &nbsp; tbl.Stores.value('Description[1]', 'nvarchar(20)') AS Description,&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; tbl2.ItemIds.value('.', 'nvarchar(20)') AS OptionalItemIds,&nbsp; &nbsp; tbl.Stores.value('Calories[1]', 'nvarchar(20)') AS Calories,&nbsp; &nbsp; tbl.Stores.value('Name[1]', 'nvarchar(20)') AS NameFROM&nbsp;&nbsp; &nbsp; @XmlProduct.nodes('/Stores/Products') AS tbl(Stores)CROSS APPLY tbl.Stores.nodes('OptionalItemIds') AS tbl2(ItemIds)