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