Accessing list in xml

Question

XML Data structure:

<CPT xmlns="http://www.example.org/genericClientProfile" xmlns:ns2="http://www.bosch-si.com/finance/cts/cpt/2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/genericClientProfile genericClientProfile.xsd">
<header>
    <serviceId>CPT-UK</serviceId>
    <versionId>1.0</versionId>
    <brandCode>RBS.ADA</brandCode>
    <creationTime>2013-09-24T16:56:52.985+02:00</creationTime>
</header>
<clientProfile bpKey="19933" id="1bb26568-1df3-4206-8cea-fb4614bf0a6a" createdBy="BARBOURT" lastModifiedBy="MANNC" documentStructureVersion="V3_2" lastModifiedAt="2013-09-23 15:40:49.873" createdAt="2013-09-23 10:07:33.608">
<section xmlns="" id="cd21fbb5-da1b-485d-8909-a8392fd5ad5c" name="globalClientFacts">
    <attribute name="familyAndDependentComment" type="string">Tomas is 18 in 2013</attribute>
<list name="familyAndDependents">
    <entry entryId="1">
        <attribute name="famDependAge" type="decimal">0</attribute>
        <attribute name="famDependBirthdate" type="date" />
        <attribute name="famDependFinDep" type="boolean">false</attribute>
        <attribute name="famDependFinDepUntil" type="string" />
        <attribute name="famDependName" type="string">Tomas</attribute>
        <attribute name="famDependNat" type="xmlCountry">GB</attribute>
        <attribute name="famDependRel" type="enumeration">SON</attribute>
        <attribute name="famDependRelTo" type="string" />
    </entry>
<entry entryId="2"></list>
<attribute name="subCommentWills" type="string" />
</section>

I have the following data structure, What i'm looking to do is create SQL insert statements from each "section" which i have managed to do. However If a section contains a list, i need to get the list name and then the attributes for only one of the entries. Currently it seems to double the attributes as there are two entries.

C#:

    public void findAllNodes(XDocument doc, XNamespace ns)
    {
        StringBuilder attribute = new StringBuilder();
        StringBuilder values = new StringBuilder();
        values.Append("(");
        var db = cptsqlentity();

        foreach (var f in doc.Descendants(ns + "clientProfile").First().Elements())
        {
            if (f.Attribute("name") != null)
            {
                values.Append(")");
                attribute.Append(")");
                attribute.Append(values);
                values.Clear();
                attribute.Append(Environment.NewLine);
                attribute.Append("INSERT INTO ");
                values.Append(" VALUES (");

                    OracleCommand oracleq = new OracleCommand("SELECT TABLE_SHORT FROM LOOKUP WHERE TABLE_LONG = " + "'" + f.Attribute("name").Value + "'" + " AND ROWNUM <=1", db);
                    OracleDataReader dr = oracleq.ExecuteReader();
                    dr.Read();
                    attribute.Append(dr.GetString(0));
                    attribute.Append(" (");
            }

            foreach (var p in f.Descendants())
            {
                if (p.Attribute("name") != null) {
                    attribute.Append(p.Attribute("name").Value + ",");
                    values.Append("'" + p.Value + "'" + ",");
                }
            }
        }

Answer 1

I'm not 100% sure about the schema for your XML file, but wouldn't the following do?

Replace your last foreach statement with the following:

foreach (var p in f.Elements("attribute")
                   .Union(f.Elements("list")
                           .SelectMany(l => l.Elements()
                                             .First()
                                             .Elements("attribute"))))
{
   //...
}

This gives you all attribute children of the section element together with all attribute children of the first child of all list elements (which are a child of your section).

Does that make sense?