SQL XQuery hierarchy help

Question

Very new to XQuery, I have an XML file that is the definition of a form on a mobile app. I need to read the xml to pull out the parent / child relationships where certain criteria is met, and they need to be ordered as they are ordered on the form (so the order in the xml document).

The XML structure is like Form > Elements > Element, but each Element tag could in theory have another nested set of Elements, and there isn't a limit of how many levels it could be. The elements themselves have ID, Text, condition tags within them. For example the first set of Elements might contain an element which is a section of the form. Then There could be a set of questions and then potentially a sub section with more questions and so on.

What I'm ideally after is a table which lists all elements in the order they appear. So the first Element, followed by it's children, and children's children, then the next top level element, followed by it's children and childrens children.

Element ID Element Ref conditions ParentID
12 SectionA 0 NULL
1 SectionA - Q1 4 12
123 SectionB 0 NULL
124 SectionB - Q1 0 123

Answer 1

Check an example:

declare @xml xml = '
<Form>
    <Elements>
        <Element ID="12" Text="SectionA" Condition="0">
            <Element ID="1" Text="Q1" Condition="4">
            </Element>
        </Element>
        <Element ID="123" Text="SectionB" Condition="0">
            <Element ID="124" Text="Q1" Condition="0">
            </Element>
        </Element>
    </Elements>
</Form>
'

select 
    r.value('@ID', 'int') as [Element ID],
    concat(r.value('../@Text', 'varchar(max)') + ' - ', r.value('@Text', 'varchar(max)')) as [Element Ref],
    r.value('@Condition', 'varchar(max)') as [Condition],
    r.value('../@ID', 'int') as [Parent ID]
from @xml.nodes('//Element') t(r)

It assumes that Element Ref is a concatenation between parent and child.

Give more details, if any.