I am new to C# and I am developing a code in C# which reads data from a variable which has data in XML format.

Some nodes are repeating and my code reads only first record of the repeating node.

My XML example:

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

Piece of code I tried:

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

var xmlNode_Product = new XElement("Stores",
           from Product in ProductList
           select new XElement("Products",
                          new XElement("Size", Product.Size),
                          new XElement("ShortName", Product.ShortName),
                          new XElement("Id", Product.Id),
                          new XElement("Description", Product.Description),
                          new XElement("OptionalItemIds", Product.OptionalItemIds),
                          new XElement("Calories", Product.Calories),
                          new XElement("Name", Product.Name)
                      ));

Next I am calling this stored procedure to enter data into the database:

INSERT INTO Products(Size, ShortName, Id, Description, OptionalItemIds, Calories, Name)
    SELECT  
        tbl.Stores.value('Size[1]', 'nvarchar(20)') AS Size,
        tbl.Stores.value('ShortName[1]', 'nvarchar(20)') AS ShortName,
        tbl.Stores.value('Id[1]', 'nvarchar(250)') AS Id,
        tbl.Stores.value('Description[1]', 'nvarchar(20)') AS Description,
        tbl.Stores.value('OptionalItemIds.[1]', 'nvarchar(250)') AS OptionalItemIds,      
        tbl.Stores.value('Calories[1]', 'nvarchar(20)') AS Calories,
        tbl.Stores.value('Name[1]', 'nvarchar(20)') AS Name
    FROM 
        @XmlProduct.nodes('/Stores/Products') AS tbl(Stores);

Result I am getting:

    Size    ShortName   ID    Description   OptionalItemIds Calories    Name
    Small   Coke        000001  Drinks          100001           0       Diet Coke

Expected results:

    Size    ShortName   ID    Description   OptionalItemIds Calories    Name
    Small   Coke        000001  Drinks          100001           0       Diet Coke
    Small   Coke        000001  Drinks          100002           0       Diet Coke
    Small   Coke        000001  Drinks          100003           0       Diet Coke
    Small   Coke        000001  Drinks          100004           0       Diet Coke
    Small   Coke        000001  Drinks          100005           0       Diet Coke

2 Answers

0
marc_s On Best Solutions

You need to use CROSS APPLY and get the enumeration of <OptionalItemIds> separately - try something like this:

INSERT INTO Products(Size, ShortName, Id, Description, OptionalItemIds, Calories, Name)
    SELECT  
        tbl.Stores.value('Size[1]', 'nvarchar(20)') AS Size,
        tbl.Stores.value('ShortName[1]', 'nvarchar(20)') AS ShortName,
        tbl.Stores.value('Id[1]', 'nvarchar(250)') AS Id,
        tbl.Stores.value('Description[1]', 'nvarchar(20)') AS Description,
        -- tbl.Stores.value('OptionalItemIds.[1]', 'nvarchar(250)') AS OptionalItemIds,      
        tbl2.ItemIds.value('.', 'int') AS OptionalItemId,
        tbl.Stores.value('Calories[1]', 'nvarchar(20)') AS Calories,
        tbl.Stores.value('Name[1]', 'nvarchar(20)') AS Name
    FROM 
        @XmlProduct.nodes('/Stores/Products') AS tbl(Stores)
    -- get the list of "OptionalItemIds" separately, cross apply to produce rows to be inserted
    CROSS APPLY
        @XmlProduct.nodes('/Stores/Products/OptionalItemIds') AS tbl2(ItemIds)
;
0
Thejaswi Shasthri On

This code worked for me,

    INSERT INTO Products(Size, ShortName, Id, Description, OptionalItemIds, Calories, Name)
SELECT  
    tbl.Stores.value('Size[1]', 'nvarchar(20)') AS Size,
    tbl.Stores.value('ShortName[1]', 'nvarchar(20)') AS ShortName,
    tbl.Stores.value('Id[1]', 'nvarchar(250)') AS Id,
    tbl.Stores.value('Description[1]', 'nvarchar(20)') AS Description,     
    tbl2.ItemIds.value('.', 'nvarchar(20)') AS OptionalItemIds,
    tbl.Stores.value('Calories[1]', 'nvarchar(20)') AS Calories,
    tbl.Stores.value('Name[1]', 'nvarchar(20)') AS Name
FROM 
    @XmlProduct.nodes('/Stores/Products') AS tbl(Stores)
CROSS APPLY tbl.Stores.nodes('OptionalItemIds') AS tbl2(ItemIds)