Stuck trying to backup XML from QuestionMark software to SharePoint

[madhatter13]

We use software called QuestionMark to manage exams for our training programs. Unfortunately, the only way to export questions from this software is in a custom XML they call QML. Just in case our contract with this vendor ever ends, we'd like to be able to backup our question banks.

The QML that is exported looks like this:

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<!DOCTYPE QML SYSTEM "QML_V3.dtd">
<QML>
  <QUESTION ID="100000251554" DESCRIPTION="Test Question 1" TOPIC="QML Export Questions (For Testing)" STATUS="Normal">
    <TAG NAME="Question Code"><![CDATA[001]]></TAG>
    <TAG NAME="Session"><![CDATA[Category1]]></TAG>
    <TAG NAME="Session"><![CDATA[Category2]]></TAG>
    <CONTENT TYPE="text/html"><![CDATA[<strong>Test Question 1</strong>]]></CONTENT>
    <ANSWER QTYPE="MC" COMMENT="YES" COMMENTLABEL="Add a comment please" COMMENTWIDTH="25" COMMENTHEIGHT="4" SUBTYPE="VERT">
      <CHOICE ID="0">
        <CONTENT TYPE="text/html"><![CDATA[Example <strong>Answer 1</strong>]]></CONTENT>
      </CHOICE>
      <CHOICE ID="1">
        <CONTENT TYPE="text/html"><![CDATA[Example <strong>Answer 2</strong>]]></CONTENT>
      </CHOICE>
      <CHOICE ID="2">
        <CONTENT TYPE="text/html"><![CDATA[Example <em>Answer 3</em>]]></CONTENT>
      </CHOICE>
      <CHOICE ID="3">
        <CONTENT TYPE="text/html"><![CDATA[Example <u>Answer 4</u>]]></CONTENT>
      </CHOICE>
    </ANSWER>
    <OUTCOME ID="0 Example Answer 1" SCORE="0">
      <CONDITION>"0"</CONDITION>
      <CONTENT TYPE="text/html"><![CDATA[WRONG. You got this WRONG.]]></CONTENT>
    </OUTCOME>
    <OUTCOME ID="1 Example Answer 2" SCORE="1">
      <CONDITION>"1"</CONDITION>
      <CONTENT TYPE="text/html"><![CDATA[That's correct!]]></CONTENT>
    </OUTCOME>
    <OUTCOME ID="2 Example Answer 3" SCORE="0">
      <CONDITION>"2"</CONDITION>
      <CONTENT TYPE="text/html"><![CDATA[WRONG. You got this WRONG.]]></CONTENT>
    </OUTCOME>
    <OUTCOME ID="3 Example Answer 4" SCORE="0">
      <CONDITION>"3"</CONDITION>
      <CONTENT TYPE="text/html"><![CDATA[WRONG. You got this WRONG.]]></CONTENT>
    </OUTCOME>
  </QUESTION>
</QML>

So for each question, we have a set of answers and then a corresponding set of outcomes (which includes the score for that answer and an accompanying explanation).

I've built a flow that will convert this XML to JSON and then parse it for use in some apply to each loops:

The outer loop creates a SharePoint item for each question in a list called QuestionContent. The inner loop creates an item for each answer to that question in a list called AnswerContent.

Now, here's where I'm stuck. I don't know how to get information from the second sub-array called "Outcome" which also needs to be added to each item in AnswerContent. They're loosely keyed, in that each "Outcome" element has an ID field that is a concatenation of the Answer ID (single integer) and the Answer text. Also, I'm sure this is important, the arrays are always presented in the correct order so that the first Answer (the Choices array) corresponds to the first element in the Outcome array.

Where should I go from here?

[grantjenkins]

Are you able to show what you would expect to see in your AnswerContent List (columns/values)?

Also, would you potentially have multiple Questions within the JSON, or will it only every be a single question at a time?

<QML>
  <QUESTION ID="11111" DESCRIPTION="Test Question 1">
    ...
  </QUESTION>
  <QUESTION ID="22222" DESCRIPTION="Test Question 2">
    ...
  </QUESTION>
</QML>

[madhatter13]

QuestionMark allows exporting individual questions or multiple questions in a single QML file, just as you have it in that example.

The AnswerContent list will ideally have the following columns:

• QuestionMark ID
  • Lookup column. This is a lookup to a column of the same name in the QuestionContent list, which contains the Question ID provided in the payload.
• Answer ID
  • Short text. The QML includes a single digit integer as an ID for each answer. I am also considering concatenating the Question ID and Answer ID (e.g., 11111-0, 11111-1, etc.). The latter would be helpful if I need to this column to be used as a lookup by a third list.
• Answer
  • Long text. Contains the plain text of the answer choice.
• Score
  • Integer. 0 or 1. 1 indicates the correct answer.
• Explanation
  • Long text. Contains the explanation that is provided to the student if they select this answer.

[madhatter13 · Accepted solution]

I was able to solve the issue by re-designing the flow from the ground up.

1. I initialize a few different array and integer variables.
2. I loop through each Question and:
   1. Gather arrays of the metadata tags ('Question Code' and 'Sessions')
   2. Create the SharePoint item in the QuestionContent list, filling in with the question stem and metadata tags
   3. Start an inner loop to create the corresponding items in the AnswerContent list
      1. In each of the iteration of this loop, append the list item ID to an array variable so we can update them in a follow-up loop
   4. Start a second inner loop that loops through the Outcome information (Score and Explanation), using the array variable from the first loop and an incrementing counter variable to know which AnswerContent list item to update

Before the loops:

Outer loop:

Inner loops: