Unconcatenate cell value

[omnomnom]

Hi everyone!

i have  field like this

<data><field id="1084"/><field id="1085">109</field><field id="1087">0</field><field id="1088">1</field></data>

how extract it to

ID	Vaule
1184	null
1185	1109
1187	0
1188	1

?

[MarcoWedel · Accepted solution]

Hi,

one possible solution:

tabInput:
LOAD *,
     RecNo() as RecNo
INLINE [
    field
    '<data><field id="1084"/><field id="1085">109</field><field id="1087">0</field><field id="1088">1</field></data>'
];

tabOutput:
LOAD RecNo,
     SubField(SubField, '"', 1) as ID,
     TextBetween(SubField, '>', '<', 1) as Value
Where SubField <> '<data>';
LOAD RecNo,
     SubField(field, '<field id="') as SubField
Resident tabInput;

hope this helps

regards

Marco