How to query XML data with repeating rows in Excel

New Contributor

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>
<_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>
<_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>


2 Replies
best response confirmed by xeno85 (New Contributor)


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



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.