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])
