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
