Unsure how to combine data

[JamesLeach]

Hello! 

I've got some survey results data I'm querying via a web API that is being returned as nested XML, many layers deep.  I'm having some problems with the last, lowest layer.     Here is a visual representation of the information: 

My 'SurveyData' table has the list of response.Ids and a column for each question's response:

response.Id	questionData.1	questionData.2	question.Data.3
1001	[record]	[record]	[record]
1002	[record]	[record]	[record]

I can expand each of those records, which is what I've done, but that leaves me with my "Answers" table, a table that looks more or less like this, which just seems wrong...

response.Id

q1.number

q1.section

q1.questionText

q1.answer

q2.number

q2.section

q2.questionText

q2.answer

q3.number

q3.section

q3.questionText

q3.answer

q4.number

q4.section

q4.questionText

q4.answer

1001

1

section one

Generic guestion one

A

2

section two

Generic question two

C

3

section three

Generic question three

D

4

section three

Generic question four

A

1002

1

section one

Generic guestion one

B

2

section two

Generic question two

C

3

section three

Generic question three

A

4

section three

Generic question four

C

1003

1

section one

Generic guestion one

A

2

section two

Generic question two

D

3

section three

Generic question three

A

4

section three

Generic question four

C

1004

1

section one

Generic guestion one

A

2

section two

Generic question two

C

3

section three

Generic question three

A

4

section three

Generic question four

A

When what I really want is something more like this:

response.Id	question.number	question.section	question.text	question.answer
1001	1	section one	Generic question one	A
1001	2	section two	Generic question two	C
1001	3	section three	Generic question three	D
1001	4	section three	Generic question four	A
1002	1	section one	Generic question one	B
1002	2	section two	Generic question two	C
1002	3	section three	Generic question three	A
1002	4	section three	Generic question four	C

I've already got the question numbers and text in another table so I could leave the question text out but the only place to get the "section" is from the responses.

Does anyone have any suggestions for how to accomplish this?

[MFelix]

Hi @JamesLeach ,

What is the connector you are using to open the file? I tried using the XML connector and it opened my test file correctly.

Is your XML with the correct output format?

Can you share a sample of your file to make some tests?

Regards,

MFelix

[JamesLeach]

It wasn't an issue with opening the data - the XML connector works fine.    The issue was with the deeply nested structure of the XML.   

I ended up with a table with an index column and columns containing records for each result.   This was what I was trying to combine.   What did was 'unpivot' all the columns with the records and then expanding the records.   This resolved my issue.

I know I did a poor job explaining what I was looking for.  

Thank you for trying to help.