XML File Loading as One Row Instead of Many

haymarketpaul — Wed, 25 Nov 2020 16:16:04 GMT

QV12 SR4

I've been handed an XML file (see attached example) that contains Firstname, Lastname, address, telephone etc but when loading - instead of giving me columns like Firstname, Lastname etc it seems to load everything in one row.

I've seen other similar posts but my knowledge of XML is next to nothing so am not sure whether there is some way i can load this effectively? Any pointers much appreciated.

My load script ends up looking like this.....

root:
LOAD [Item5/organisation_name],
    [Item5/organisation_address],
    [Item5/org_id],
    [Item5/people_count],
    [Item5/contacts/Item1/person_id],
    [Item5/contacts/Item1/name],
    [Item5/contacts/Item1/first_name],
    [Item5/contacts/Item1/last_name],
    [Item5/contacts/Item1/email/Item0],
    [Item5/contacts/Item1/phone/Item0],
    [Item5/contacts/Item0/person_id],
    [Item5/contacts/Item0/name],
    [Item5/contacts/Item0/first_name],
    [Item5/contacts/Item0/last_name],
    [Item5/contacts/Item0/email/Item0],
    [Item5/contacts/Item0/phone/Item0],
    [Item4/organisation_name],
    [Item4/org_id],
    [Item4/people_count],
    [Item4/contacts/Item0/person_id],
    [Item4/contacts/Item0/name],
    [Item4/contacts/Item0/first_name],
    [Item4/contacts/Item0/last_name],
    [Item4/contacts/Item0/email/Item0],
    [Item4/contacts/Item0/phone/Item0],
    [Item3/organisation_name],
    [Item3/organisation_address],
    [Item3/org_id],
    [Item3/people_count],
    [Item3/contacts/Item1/person_id],
    [Item3/contacts/Item1/name],
    [Item3/contacts/Item1/first_name],
    [Item3/contacts/Item1/last_name],
    [Item3/contacts/Item1/contact_notes/Item1/timestamp] as [contact_notes/Item1/timestamp],
    [Item3/contacts/Item1/contact_notes/Item1/note] as [contact_notes/Item1/note],
    [Item3/contacts/Item1/contact_notes/Item0/timestamp] as [contact_notes/Item0/timestamp],
    [Item3/contacts/Item1/contact_notes/Item0/note] as [contact_notes/Item0/note],
    [Item3/contacts/Item1/email/Item0],
    [Item3/contacts/Item1/phone/Item0],
    [Item3/contacts/Item1/phone/Item1] as [Item1/phone/Item1],
    [Item3/contacts/Item0/person_id],
    [Item3/contacts/Item0/name],
    [Item3/contacts/Item0/first_name],
    [Item3/contacts/Item0/last_name],
    [Item3/contacts/Item0/email/Item0],
    [Item3/contacts/Item0/phone/Item0],
    [Item3/contacts/Item0/phone/Item1],
    [Item3/organisation_notes/Item11/timestamp] as [Item11/timestamp],
    [Item3/organisation_notes/Item11/note] as [Item11/note],
    [Item3/organisation_notes/Item10/timestamp] as [Item10/timestamp],
    [Item3/organisation_notes/Item10/note] as [Item10/note],
    [Item3/organisation_notes/Item9/timestamp] as [Item9/timestamp],
    [Item3/organisation_notes/Item9/note] as [Item9/note],
    [Item3/organisation_notes/Item8/timestamp] as [Item8/timestamp],
    [Item3/organisation_notes/Item8/note] as [Item8/note],
    [Item3/organisation_notes/Item7/timestamp] as [Item7/timestamp],
    [Item3/organisation_notes/Item7/note] as [Item7/note],
    [Item3/organisation_notes/Item6/timestamp] as [Item6/timestamp],
    [Item3/organisation_notes/Item6/note] as [Item6/note],
    [Item3/organisation_notes/Item5/timestamp] as [Item5/timestamp],
    [Item3/organisation_notes/Item5/note] as [Item5/note],
    [Item3/organisation_notes/Item4/timestamp] as [Item4/timestamp],
    [Item3/organisation_notes/Item4/note] as [Item4/note],
    [Item3/organisation_notes/Item3/timestamp] as [Item3/timestamp],
    [Item3/organisation_notes/Item3/note] as [Item3/note],
    [Item3/organisation_notes/Item2/timestamp] as [Item2/timestamp],
    [Item3/organisation_notes/Item2/note] as [Item2/note],
    [Item3/organisation_notes/Item1/timestamp] as [organisation_notes/Item1/timestamp],
    [Item3/organisation_notes/Item1/note] as [organisation_notes/Item1/note],
    [Item3/organisation_notes/Item0/timestamp] as [organisation_notes/Item0/timestamp],
    [Item3/organisation_notes/Item0/note] as [organisation_notes/Item0/note],
    [Item2/organisation_name],
    [Item2/org_id],
    [Item2/people_count],
    [Item2/contacts/Item0/person_id],
    [Item2/contacts/Item0/name],
    [Item2/contacts/Item0/first_name],
    [Item2/contacts/Item0/last_name],
    [Item2/contacts/Item0/email/Item0],
    [Item2/contacts/Item0/phone/Item0],
    [Item2/contacts/Item0/phone/Item1],
    [Item1/organisation_name],
    [Item1/organisation_address],
    [Item1/org_id],
    [Item1/people_count],
    [Item1/contacts/Item0/person_id],
    [Item1/contacts/Item0/name],
    [Item1/contacts/Item0/first_name],
    [Item1/contacts/Item0/last_name],
    [Item1/contacts/Item0/email/Item0],
    [Item1/contacts/Item0/phone/Item0],
    [Item0/organisation_name],
    [Item0/org_id],
    [Item0/people_count],
    [Item0/contacts/Item0/person_id],
    [Item0/contacts/Item0/name],
    [Item0/contacts/Item0/first_name],
    [Item0/contacts/Item0/last_name],
    [Item0/contacts/Item0/email/Item0],
    [Item0/contacts/Item0/phone/Item0],
    [Item0/contacts/Item0/phone/Item1]
FROM  (XmlSimple, Table is [root]);

topic XML File Loading as One Row Instead of Many in QlikView

XML File Loading as One Row Instead of Many