Hi,

I have a table (that started life as a JSON table, lists exposed etc) containing multiple attribute fields and rows. One of the fields contains CLOB data, like this:

<cmAstLocSpId>148606829</cmAstLocSpId><criticalityDetails><criticalityReasons><criticalityReasonsList><criticalityReason>CMEH</criticalityReason></criticalityReasonsList></criticalityReasons></criticalityDetails><cmOsgridref>NZ30272359</cmOsgridref><cmOsnorthing>523598</cmOsnorthing><cmOseasting>430279</cmOseasting><cmRegion>TEESSIDE</cmRegion><cmRepairRegion>TEESSIDE</cmRepairRegion><cmDnoArea>NP (NE)</cmDnoArea><cmCreatedByUser>DAVID</cmCreatedByUser><cmCreationDate>2023-11-23</cmCreationDate>

I want to turn the normal text into column / field headings, and those highlighted in bold into values in the relevant column. eg the first column will be “cmAstLocSpld” and the value will be “148606829”. The next column will be “criticalityDetails” but in this case has no value, but I still need to create a column.

I’ve added “CLOB start.xlx” to illustrate what I have now, and “CLOB end.xlsx” to illustrate where I want to end up. (This data isn’t exactly the same as the examples shown below, but principal is the same)

That is my challenge in a nutshell.

So far I have tried an AttributeSplitter with the Delimiter as >< followed by a list exploder, repeated AttributeSplitter followed by ListExploder to create 2 lists, AttributeSplitter a third time, and then a Tester to find values with “</” in them to give this type of result “148606829</cmAstLocSpId” as this needs to be the value in that column.
Another ListExploder splits the data out into single entities, whether they be the desired column names or desired values.

I am hoping there is a much more straightforward methodology and someone can give me a solid steer on how to create those field / column names.
Any advice will be warmly welcomed. Thank you in advance, Stuart.