I have a source table, each row of which has an XML field that contains an unknown number of survey responses that I need to shred and put into output tables. I've tried nodes and OPENXML and I can't seem to get it working. From my research, I'm thinking I need to use CROSS APPLY, but I can't seem to put it all together in my head in a way that I understand and get it to work. I apologize if I'm missing something simple, but I really am stuck and would appreciate any help offered for how to deal with this.
Here's the source table containing imported data (the format of which I have no control over) that looks like this:
ImportId SurveyId DateImported ResponseData
-------- -------- ------------ ------------
1 11223344 2017-05-21 18:00:00.00 <survey><data><result>...
2 55667788 2017-05-21 18:01:00.00 <survey><data><result>...
3 99009988 2017-05-21 18:02:00.00 <survey><data><result>...
Here's a simplified example of the XML in the ResponseData column:
<survey>
<data>
<result>
<id>12345</id>
<date_submitted>2017-05-10 09:30:15</date_submitted>
<url_variables>
<respondent_id>
<key>respondent_id</key>
<value>987654</value>
<type>url</type>
</respondent_id>
<respondent_level>
<key>respondent_level</key>
<value>5</value>
<type>url</type>
</respondent_level>
</url_variables>
<survey_data>
<question>
<id>1</id>
<answer>Yes</answer>
</question>
<question>
<id>2</id>
<answer>No</answer>
</question>
<question>
<id>3</id>
<subquestions>
<subquestion>
<id>4</id>
<answer>Maybe</answer>
</subquestion>
<subquestion>
<id>5</id>
<answer>I don't know</answer>
</subquestion>
</subquestions>
</question>
... more questions ...
</survey_data>
</result>
<result>
<id>67890</id>
<date_submitted>2017-05-11 10:00:00</date_submitted>
<url_variables>
<respondent_id>
<key>respondent_id</key>
<value>34567</value>
<type>url</type>
</respondent_id>
<respondent_level>
<key>respondent_level</key>
<value>10</value>
<type>url</type>
</respondent_level>
</url_variables>
<survey_data>
<question>
<id>1</id>
<answer>No</answer>
</question>
<question>
<id>2</id>
<answer>Yes</answer>
</question>
<question>
<id>3</id>
<subquestions>
<subquestion>
<id>4</id>
<answer>Definitely not</answer>
</subquestion>
<subquestion>
<id>5</id>
<answer>I object</answer>
</subquestion>
</subquestions>
</question>
... more questions ...
</survey_data>
</result>
... more results ...
<data>
</survey>
I need to take this data, shredding the XML to get multiple results in each ResponseData field and put it in two tables like these:
ResultId SurveyId RespondentId RespondentLevel DateSubmitted
-------- -------- ------------ --------------- -------------
12345 11223344 987654 5 2017-05-10 09:30:15
67890 11223344 34567 10 2017-05-11 10:00:00
...
(Data extracted from the rest of ImportId 1 followed by ImportId 2, 3, etc)
ResultId QuestionId SubquestionId Answer
---------- ---------- ------------- ------
12345 1 0 Yes
12345 2 0 No
12345 3 4 Maybe
12345 3 5 I don't know
67890 1 0 No
67890 2 0 Yes
67890 3 4 Definitely not
67890 3 5 I object
...
(Data extracted from the rest of ImportId 1 followed by ImportId 2, 3, etc)
For Two Tables, you would need two queries
Example (I used only 1 record but will apply to multiple)
Returns