How to query XML data with repeating rows in Excel

[xeno85]

Hey there,

I am capturing XML data from Nintex into a SharePoint list that I am then exporting to an Excel spreadsheet. Below I've captured an example of what the XML data looks like in a cell in the Excel spreadsheet.

I can use the FILTERXML function to capture entries that have only 1 entry in it by using the following formula =FILTERXML(D2,"//Item/_3cc4ee36ff3e3c3127264282cdae23e4"). However when I get multiple entries in a form, I come across a #SPILL! error.

Is there a formula I could use to query the example below and bring out multiple values?

I am not fussy in how it's displayed but to make it easy to read in a bulk fashion. Open to all suggestions!

Appreciate any help and guidance!

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version>1.0</Version>
<Items>
<Item>
<_3cc4ee36ff3e3c3127264282cdae23e4 type="System.String">9999246896</_3cc4ee36ff3e3c3127264282cdae23e4>
<_03a02b14c271b78c8a75296f53738207 type="System.String">SP65</_03a02b14c271b78c8a75296f53738207>
<_ded503a20f4357c45ff658b2c662f677 type="System.String">353</_ded503a20f4357c45ff658b2c662f677>
<_aa5865bc9b8a31fc0c4e27b68e9689d9 type="System.String">12308</_aa5865bc9b8a31fc0c4e27b68e9689d9>
<_a3b2486784f60c9c562209860ac2b7b9 type="System.DateTime">2022-06-05T14:00:00.000Z</_a3b2486784f60c9c562209860ac2b7b9>
</Item>
<Item>
<_3cc4ee36ff3e3c3127264282cdae23e4 type="System.String">9999397307</_3cc4ee36ff3e3c3127264282cdae23e4>
<_03a02b14c271b78c8a75296f53738207 type="System.String">FTM</_03a02b14c271b78c8a75296f53738207>
<_ded503a20f4357c45ff658b2c662f677 type="System.String">0098</_ded503a20f4357c45ff658b2c662f677>
 <_aa5865bc9b8a31fc0c4e27b68e9689d9 type="System.String">5262</_aa5865bc9b8a31fc0c4e27b68e9689d9>
 <_a3b2486784f60c9c562209860ac2b7b9 type="System.DateTime">2022-06-05T14:00:00.000Z</_a3b2486784f60c9c562209860ac2b7b9>
</Item>
</Items>
</RepeaterData>

[#2 · Accepted solution]

@xeno85 

You can wrap your FILTERXML() formula by TEXTJOIN() to capture multiple values to a single cell.

=TEXTJOIN(",",TRUE,FILTERXML(D2,"//Item/_3cc4ee36ff3e3c3127264282cdae23e4"))

[#3]

Hey Harun,
You are a legend!! Thank you so much!! I didn't even consider this option. You've just made someone immensely happy! Appreciate the prompt response mate.