Need help with a Linq XML conditional grouping query
- by FiveTools
I have the following xml fragment:
<BANNER ID="Banner 2" ROW_WIDTH="200">
<BANNER_TEXTS ID="BANNER_TEXTS">
<BANNER_TEXT UNDERLINE="false" SPAN_COL="1" WIDTHT="78px"></BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="3" WIDTHT="234px">Years In Practice</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="3" WIDTHT="234px">Internet Usage</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="4" WIDTHT="312px">Sales Reps Seen / Week</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="3" WIDTHT="234px">Prescription Volume</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="3" WIDTHT="222px">Patient Load</BANNER_TEXT>
</BANNER_TEXTS>
<BANNER_TEXTS ID="COLUMN_TEXTS">
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">Total</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">< 11 years</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">11-20 years</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">21-30 years</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">Light 1-5 hrs</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">Medium 6-10 hrs</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">Heavy >10 hrs</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">0</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">1-2</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">3-5</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">>5</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">1-100</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">101-150</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="78px">>150</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="74px">1-100</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="74px">101-200</BANNER_TEXT>
<BANNER_TEXT UNDERLINE="true" SPAN_COL="1" WIDTHT="74px">>200</BANNER_TEXT>
</BANNER_TEXTS>
<BANNER_TEXTS ID="COLUMN_TEXTS">
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(A)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(B)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(C)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(D)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(E)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(F)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(G)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(H)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(I)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(J)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(K)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(L)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(M)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(N)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(O)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(P)</COLUMN_TEXT>
<COLUMN_TEXT UNDERLINE="false" SPAN_COL="1">(Q)</COLUMN_TEXT>
</BANNER_TEXTS>
</BANNER>
I would like to group all the 'BANNER_TEXT' in the second sequence using the first sequence 'BANNER_TEXT' as the key (only include elements where string is not null or empty). The span_col attribute in the first 'BANNER_TEXT' sequence indicates which elements by position in the 2nd sequence are related.
An example: 'Years in Practice' would be the first key and the attribute SPAN_COL=3 for that element indicates it would contain '< 11 years', '11-20 years', '21-30 years' (the first grouping of string.empty = Total would be skipped).
