Formatting IMPORTXML Xpath query into readable data for Google Sheets-CodePudding

I am importing XML data to a google sheet which has the following structure: edit: URL:

CodePudding user response：

try:

=INDEX(SUBSTITUTE(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(FLATTEN(QUERY(TRANSPOSE(
 IFERROR(IF(0=MOD(ROW(A:A)-1, 5)-{0, 1, 4, 3, 2}, {"♦","","","",""}&TEXT(
 IMPORTXML(A1, "//data/campaignId|//data/campaignName|//data/startDate/year|//data/startDate/month|//data/startDate/day"), 
 {"@","@","@","@","@"})&{"♠","♠","","♣","♣"}, ))),,9^9)),,9^9), "♦")), "♠")), "♣ ", "/"))