How to convert complex XML data to tabular format

Anonymous — Tue, 06 Mar 2018 15:16:57 GMT

I have this XML file which I extracted from my cmc server. I am showing you the sample xml file how it looks like.

I want to transform my xml into something like this.

NAME	ID	CUID	PARENTID	USERGROUPS
John	23	sdfsdfsd	21	1
emily	342	asfvsed	21	101
emily	342	asfvsed	21	432
emily	342	asfvsed	21	334
Oliver	47233	sdfsdfssdfs	21	1
Oliver	47233	sdfsdfssdfs	21	243

The XML seems more complicated and is becoming difficult for me to extract the data into tabular format. I was able to join name,id,cuid, parentid as they were only single values. But since for each user, there are many usergroups, it becomes difficult as QlikView creates many keys which seems unnecessary.

For name,id,cuid,parentid I used this -

LOAD RowNo() as link, attr%Table as ID

FROM [Sourcepath] (XmlSimple, Table is [feed/entry/content/attrs/attr])

Where name = 'ID';

Left Join Load RowNo() as link, attr%Table as CUID

FROM [Sourcepath] (XmlSimple, Table is [feed/entry/content/attrs/attr])

where name = 'CUID';

Left Join LOAD RowNo() as link, attr%Table as NAME

FROM [Sourcepathl] (XmlSimple, Table is [feed/entry/content/attrs/attr])

Where name = 'NAME';

Left Join LOAD RowNo() as link, attr%Table as PARENTID

FROM [Sourcepath] (XmlSimple, Table is [feed/entry/content/attrs/attr])

Where name = 'PARENTID';

Could anyone help in formatting the usergroups.

Thank you - vaibhav borkar

topic How to convert complex XML data to tabular format in QlikView

How to convert complex XML data to tabular format