How to join different level of nodes with xml.linq into one dataset row? Need someone to check code.

dejandejanovic
dejandejanovic used Ask the Experts™
on
Hello,
I'm studying Linq to xml, and have below project. Codes is more or less working fine until I choose different level of nodes. Below examples I hope will explain everything.
So, how to join different level of nodes as one table record.

XmlFile:
<books>
    <book ID="0" published="2014">
        <title>Linq2Xml</title>
        <author>
            <first-name>John</first-name>
            <last-name>Brown</last-name>
        </author>
        <publisher>Amazon</publisher>
        <price>50.00</price>
    </book>
</books>

Open in new window




Dim query = From st In statesXml...<author> _
                    Select New With { _
                        .ID = st.@ID,
                        .published = st.@published,
                        .Name = st.<FirstName>.Value _
                                    }
        DataGridView1.DataSource = query.ToList

Open in new window

Return only FirstName Value, like:
ID  published             FirstName
0   | 2014           | <emtpycolumn>






Dim query = From st In statesXml...<book> _
                    Select New With { _
                        .ID = st.@ID,
                        .published = st.@published,
                        .Name = st.<FirstName>.Value _
                                    }
        DataGridView1.DataSource = query.ToList

Open in new window

Return only FirstName Value, like:
ID                               published             FirstName
<emptycolumn> | <emtpycolumn> | John


Thanks in advance for help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Retired
Distinguished Expert 2017
Commented:
Hi dejandejanovic;

The following code snippet should get you what you need. I am not very proficient using the axis operators to get around the document but I can not find axis operater to access the ancestor of a node so the following starts at the ancestor node, book.

Dim query = From st In statesXml...<book> _
             Select New With { _
                .ID = st.@ID, _
                .published = st.@published, _
                .Name = st.<author>.<first-name>.Value _
            }

Open in new window


Please be aware that capitalization in an Linq to XML document is required to access nodes and attributes even though it's not required in Visual Basic to access variables in code.

Author

Commented:
Fernando,
works perfect.  Thank you...

I was also try to compose similar as your solution <node>.<childnode>, but somehow I just could not understand, and confgure the syntax.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
You may have had an issue with the spelling of the node name and the capitalization of the node name. For example in your post you used FirstName where in the XML document it was spelled as first-name, and was spelled in lower case characters.

Author

Commented:
Yes, I have put in knowledge naming, and casing.
Comparison is wrong as I was very quickly translating from my language to EN, etc... :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial