By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @XML XML = '
<StiSerializer version="1.02" type="Net" application="StiReport">
<Pages isList="true" count="1">
<Page1 Ref="3" type="Page" isKey="true">
<Components isList="true" count="12">
<GroupHeaderBand1 Ref="18" type="GroupHeaderBand" isKey="true">
<Condition>Nature</Condition>
</GroupHeaderBand1>
<GroupHeaderBand2 Ref="21" type="GroupHeaderBand" isKey="true">
<Condition>Job</Condition>
</GroupHeaderBand2>
<GroupHeaderBand3 Ref="26" type="GroupHeaderBand" isKey="true">
<Condition>Name</Condition>
</GroupHeaderBand3>
</Components>
</Page1>
</Pages>
</StiSerializer>'
SELECT
A.evnt.value('local-name(.)', 'varchar(100)') AS tag,
A.evnt.value('(Condition/text())[1]','varchar(100)') as condition
FROM @XML.nodes('StiSerializer/Pages/Page1/Components/*') A(evnt)
| tag | condition |
|---|---|
| GroupHeaderBand1 | Nature |
| GroupHeaderBand2 | Job |
| GroupHeaderBand3 | Name |
DECLARE @XML XML = '
<StiSerializer version="1.02" type="Net" application="StiReport">
<Pages isList="true" count="1">
<Page1 Ref="3" type="Page" isKey="true">
<Components isList="true" count="12">
<GroupHeaderBand1 Ref="18" type="GroupHeaderBand" isKey="true">
<Condition>Nature</Condition>
</GroupHeaderBand1>
<GroupHeaderBand2 Ref="21" type="GroupHeaderBand" isKey="true">
<Condition>Job</Condition>
</GroupHeaderBand2>
<GroupHeaderBand3 Ref="26" type="GroupHeaderBand" isKey="true">
<Condition>Name</Condition>
</GroupHeaderBand3>
</Components>
</Page1>
</Pages>
</StiSerializer>'
SELECT
A.evnt.value('local-name(.)', 'varchar(100)') AS tag,
A.evnt.value('(.)[1]','varchar(100)') as condition
FROM @XML.nodes('StiSerializer/Pages/Page1/Components/*') A(evnt)
| tag | condition |
|---|---|
| GroupHeaderBand1 | Nature |
| GroupHeaderBand2 | Job |
| GroupHeaderBand3 | Name |