XPATH - SurveyMonkey - Dynamic Controls - Field Retrieval

[Bcnewton]

Hi,

I have a SurveyMonkey Form that I am trying to retrieve data from.  Can’t get dynamic controls after parse JSON, so using xpath to extract data. 

If I use this, I get the answers:

xpath(xml(json(concat('{"root":', body('Parse_JSON'), '}'))), '//simple_text/text()')

This is the response for the answers.

["Environmental","Health","Date observed | 04/06/2023","Time | 8:00 AM","The problem is ongoing","Other | N/A","1431 & Bagdad Rd","Lat 000 Long 000","\n ZIP/Postal Code\n | 78613","Williamson County","Hatfield","Hatfield is driving up and down the road spewing black exhaust for hours.","black-smoke-exhaust.jpg","Contact me with updates.","No preference","\n Name\n | Bess New","\n Address\n | test","\n City/Town\n | Austin","\n ZIP/Postal Code\n | 78753","\n Email Address\n | bess@somedomain.com","\n Phone Number\n | 5555555555"]

If I use this, I get the questions:

xpath(xml(json(concat('{"root":', body('Parse_JSON'), '}'))), '//heading/text()')

This is the response for the questions.

["Nature of the complaint affecting you? (Select one or more)","Enter date observed:","Approximate time observed:","Is the problem still ongoing?","Address or description of the location where the problem is occurring:","Lat/Long","Please provide a 5-digit zip code of location where problem is occurring.","County where the problem is occurring:","Who or what you believe is the source of the problem:  If this is already a regulated entity, you may wish to reference the facility or customer name or number here:  TCEQ Central Registry Query","A brief description of the problem:","Upload with the Choose File button below.","Would you like to provide information for us to contact you?","If you are providing information, what is your preferred way of contact?","Contact Information"]

I cannot figure how to get the answers and questions to be associated or to get them together.  My goal would be to have each one be able to go into a column in a SharePoint list with the correct answer going to the correct question.  That seems like I would need dynamic controls, but maybe there is another way.

Plus, it all presents in an array, which then has to be separated.

Also, the body of the Parse JSON show a human download link for attachments, however I have not found a way to extract that.  Actually, none of the links are gathered with the two xpath codes I entered. The analyze data link is able to be picked up from the dynamic controls, so that is good.

Important to note that xpath is an entirely new experience for me, so I really have no understanding of it.

Below I have included the body return from the parse json.

[

  {

    "id": "124443016",

    "answers": [

      {

        "choice_id": "933395985",

        "simple_text": "Environmental"

      },

      {

        "choice_id": "933395986",

        "simple_text": "Health"

      }

    ],

    "family": "multiple_choice",

    "subtype": "vertical",

    "heading": "Nature of the complaint affecting you? (Select one or more)"

  },

  {

    "id": "124443010",

    "answers": [

      {

        "row_id": "933395971",

        "tag_data": [],

        "text": "04/06/2023",

        "simple_text": "Date observed | 04/06/2023"

      }

    ],

    "family": "datetime",

    "subtype": "date_only",

    "heading": "Enter date observed:"

  },

  {

    "id": "124443018",

    "answers": [

      {

        "row_id": "933396005",

        "tag_data": [],

        "text": "8:00 AM",

        "simple_text": "Time | 8:00 AM"

      }

    ],

    "family": "datetime",

    "subtype": "time_only",

    "heading": "Approximate time observed:"

  },

  {

    "id": "124443015",

    "answers": [

      {

        "choice_id": "933395981",

        "simple_text": "The problem is ongoing"

      },

      {

        "other_id": "933395999",

        "text": "N/A",

        "simple_text": "Other | N/A"

      }

    ],

    "family": "single_choice",

    "subtype": "vertical",

    "heading": "Is the problem still ongoing?"

  },

  {

    "id": "124443011",

    "answers": [

      {

        "tag_data": [],

        "text": "1431 & Bagdad Rd",

        "simple_text": "1431 & Bagdad Rd"

      }

    ],

    "family": "open_ended",

    "subtype": "single",

    "heading": "Address or description of the location where the problem is occurring:"

  },

  {

    "id": "124443017",

    "answers": [

      {

        "tag_data": [],

        "text": "Lat 000 Long 000",

        "simple_text": "Lat 000 Long 000"

      }

    ],

    "family": "open_ended",

    "subtype": "single",

    "heading": "Lat/Long"

  },

  {

    "id": "124443019",

    "answers": [

      {

        "row_id": "933396014",

        "tag_data": [],

        "text": "78613",

        "simple_text": "\n              ZIP/Postal Code\n             | 78613"

      }

    ],

    "family": "demographic",

    "subtype": "international",

    "heading": "Please provide a 5-digit zip code of location where problem is occurring."

  },

  {

    "id": "124443027",

    "answers": [

      {

        "choice_id": "933396284",

        "simple_text": "Williamson County"

      }

    ],

    "family": "single_choice",

    "subtype": "menu",

    "heading": "County where the problem is occurring:"

  },

  {

    "id": "124443012",

    "answers": [

      {

        "tag_data": [],

        "text": "Hatfield",

        "simple_text": "Hatfield"

      }

    ],

    "family": "open_ended",

    "subtype": "essay",

    "heading": "Who or what you believe is the source of the problem:  If this is already a regulated entity, you may wish to reference the facility or customer name or number here:  <a href=\"https://www15.tceq.texas.gov/crpub/\" rel=\"nofollow\" target=\"_blank\">TCEQ Central Registry Query</a>"

  },

  {

    "id": "124443013",

    "answers": [

      {

        "tag_data": [],

        "text": "Hatfield is driving up and down the road spewing black exhaust for hours.",

        "simple_text": "Hatfield is driving up and down the road spewing black exhaust for hours."

      }

    ],

    "family": "open_ended",

    "subtype": "essay",

    "heading": "A brief description of the problem:"

  },

  {

    "id": "124443014",

    "answers": [

      {

        "content_type": "image/jpeg",

        "tag_data": [],

        "text": "black-smoke-exhaust.jpg",

        "download_url": "REMOVED",

        "human_download_url": "https://www.surveymonkey.com/analyze/files/download/?survey_id=406622915&files=SXWswWIGBMlw_2FlPD0qw...",

        "simple_text": "black-smoke-exhaust.jpg"

      }

    ],

    "family": "open_ended",

    "subtype": "single",

    "heading": "Upload with the <em>Choose File</em> button below."

  },

  {

    "id": "124443022",

    "answers": [

      {

        "choice_id": "933396022",

        "simple_text": "Contact me with updates."

      }

    ],

    "family": "single_choice",

    "subtype": "vertical",

    "heading": "Would you like to provide information for us to contact you?"

  },

  {

    "id": "124443021",

    "answers": [

      {

        "choice_id": "933396021",

        "simple_text": "No preference"

      }

    ],

    "family": "single_choice",

    "subtype": "vertical",

    "heading": "If you are providing information, what is your preferred way of contact?"

  },

  {

    "id": "124443024",

    "answers": [

      {

        "row_id": "933396027",

        "tag_data": [],

        "text": "Bess New",

        "simple_text": "\n              Name\n             | Bess New"

      },

      {

        "row_id": "933396029",

        "tag_data": [],

        "text": "test",

        "simple_text": "\n              Address\n             | test"

      },

      {

        "row_id": "933396031",

        "tag_data": [],

        "text": "Austin",

        "simple_text": "\n              City/Town\n             | Austin"

      },

      {

        "row_id": "933396033",

        "tag_data": [],

        "text": "78753",

        "simple_text": "\n              ZIP/Postal Code\n             | 78753"

      },

      {

        "row_id": "933396035",

        "tag_data": [],

        "text": "bess@somedomain.com",

        "simple_text": "\n              Email Address\n             | bess@somedomain.com"

      },

      {

        "row_id": "933396036",

        "tag_data": [],

        "text": "5555555555",

        "simple_text": "\n              Phone Number\n             | 5555555555"

      }

    ],

    "family": "demographic",

    "subtype": "international",

    "heading": "Contact Information"

  }

]

[Bcnewton]

I thought I had it with JOIN, but it duplicated 13 times and was not totally correct.

And, still needing a way to extract each separately.

I am able to join the answer with a <br> and the questions with a <br> to give a nicer layout. However I still have not figured how to get the question and the answer together.

Join questions
Nature of the complaint affecting you? (Select one or more)
Enter date observed:
Approximate time observed:
Is the problem still ongoing?
Address or description of the location where the problem is occurring:
Lat/Long
Please provide a 5-digit zip code of location where problem is occurring.
County where the problem is occurring:
Who or what you believe is the source of the problem:  If this is already a regulated entity, you may wish to reference the facility or customer name or number here:  TCEQ Central Registry Query
A brief description of the problem:
Upload with the Choose File button below.
Would you like to provide information for us to contact you?
If you are providing information, what is your preferred way of contact?
Contact Information

join answers
Environmental
Health
Date observed | 04/06/2023
Time | 8:00 AM
The problem is ongoing
Other | N/A
1431 & Bagdad Rd
Lat 000 Long 000
ZIP/Postal Code | 78613
Williamson County
Hatfield
Hatfield is driving up and down the road spewing black exhaust for hours.
black-smoke-exhaust.jpg
Contact me with updates.
No preference
Name | Bess New
Address | test
City/Town | Austin
ZIP/Postal Code | 78753
Email Address | bess@somedomain.com
Phone Number | 5555555555

[grantjenkins]

Hopefully this will get you a bit closer to what you need.

See full flow below. I'll go into each of the actions.

Data is a Compose that contains your data.

XML is a Compose that converts the Data to XML.

xml(json(concat('{"root": { value:', outputs('Data'), '}}')))

Select uses the output from Data and the following expressions.

//Id
item()?['id']

//Question
xpath(
    outputs('XML'), 
    concat('string(//root/value[id="', item()?['id'], '"]/heading/text())')
)

//Answer (Joined with a <br>)
join(
    xpath(
        outputs('XML'), 
        concat('//root/value[id="', item()?['id'], '"]/answers/simple_text/text()')
    ), 
    '<br>'
)

If you wanted to get dynamic properties for the Select, you can use a Parse JSON using the following schema.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Id": {
                "type": "string"
            },
            "Question": {
                "type": "string"
            },
            "Answer": {
                "type": "string"
            }
        },
        "required": [
            "Id",
            "Question",
            "Answer"
        ]
    }
}

After running the flow, the Parse JSON would contain the Id, Question and joined Answers.

[Bcnewton]

@grantjenkins 

I wish it did!  When I do this, the following is the result I get from Select, even though the input has everything:

[
  {
    "id": "114289710434",
    "Question": "",
    "Answer": ""
  }
]

[grantjenkins]

Are you using your Parse JSON output (which is the output I used in my example) as input for the XML step?

Are you able to show what you currently have setup?

[Bcnewton]

@grantjenkins thank you for helping me.

Yes, I am using my Parse JSON output.

Here is what I am doing and what I am running into.

• I have the Parse JSON. 
• For the Data, just using the Parse JSON body creates this error in Select. “BadRequest. The 'from' property value in the 'select' action inputs is of type 'Object'. The value must be an array”  So I used createArray(body('Parse_JSON')).
• For everything else, I copied and pasted your code. You can see all of the info goes into the input of select, but does not output.
• For testing only, I also, just entered my Parse JSON data into the Compose and got the same result.
• I notice there is only one id that shows up in the XML

Also, the following is the input that I see in Select after it runs, with really no output except the one id number.

[
{
"id": "114289710434",
"recipient_id": "",
"collection_mode": "default",
"response_status": "completed",
"custom_value": "",
"first_name": "",
"last_name": "",
"email_address": "",
"ip_address": "000",
"logic_path": {},
"metadata": {
"respondent": {
"user_agent": {
"type": "number",
"value": 5446817
},
"language": {
"type": "string",
"value": "en"
}
},
"contact": {}
},
"page_path": [],
"collector_id": "425365924",
"survey_id": "406622915",
"custom_variables": {},
"edit_url": "https://www.surveymonkey.com/removed",
"analyze_url": "https://www.surveymonkey.com/removed",
"total_time": 246,
"date_modified": "2023-04-10T14:51:43+00:00",
"date_created": "2023-04-10T14:47:37+00:00",
"href": "https://api.surveymonkey.net/removed",
"questions": [
{
"id": "124443016",
"answers": [
{
"choice_id": "933395987",
"simple_text": "Oil and Gas"
},
{
"choice_id": "933395989",
"simple_text": "Property"
},
{
"choice_id": "933395990",
"simple_text": "Spill"
}
],
"family": "multiple_choice",
"subtype": "vertical",
"heading": "Nature of the complaint affecting you? (Select one or more)"
},
{
"id": "124443010",
"answers": [
{
"row_id": "933395971",
"tag_data": [],
"text": "04/10/2023",
"simple_text": "Date observed | 04/10/2023"
}
],
"family": "datetime",
"subtype": "date_only",
"heading": "Enter date observed:"
},
{
"id": "124443018",
"answers": [
{
"row_id": "933396005",
"tag_data": [],
"text": "7:00 AM",
"simple_text": "Time | 7:00 AM"
}
],
"family": "datetime",
"subtype": "time_only",
"heading": "Approximate time observed:"
},
{
"id": "124443015",
"answers": [
{
"choice_id": "933395981",
"simple_text": "The problem is ongoing"
},
{
"other_id": "933395999",
"text": "N/A",
"simple_text": "Other | N/A"
}
],
"family": "single_choice",
"subtype": "vertical",
"heading": "Is the problem still ongoing?"
},
{
"id": "124443011",
"answers": [
{
"tag_data": [],
"text": "1431 & Bagdad Rd",
"simple_text": "1431 & Bagdad Rd"
}
],
"family": "open_ended",
"subtype": "single",
"heading": "Address or description of the location where the problem is occurring:"
},
{
"id": "124443017",
"answers": [
{
"tag_data": [],
"text": "Lat 000 Long 000",
"simple_text": "Lat 000 Long 000"
}
],
"family": "open_ended",
"subtype": "single",
"heading": "Lat/Long"
},
{
"id": "124443019",
"answers": [
{
"row_id": "933396014",
"tag_data": [],
"text": "78613",
"simple_text": "\n ZIP/Postal Code\n | 78613"
}
],
"family": "demographic",
"subtype": "international",
"heading": "Please provide a 5-digit zip code of location where problem is occurring."
},
{
"id": "124443027",
"answers": [
{
"choice_id": "933396284",
"simple_text": "Williamson County"
}
],
"family": "single_choice",
"subtype": "menu",
"heading": "County where the problem is occurring:"
},
{
"id": "124443012",
"answers": [
{
"tag_data": [],
"text": "Hatfield",
"simple_text": "Hatfield"
}
],
"family": "open_ended",
"subtype": "essay",
"heading": "Who or what you believe is the source of the problem:  If this is already a regulated entity, you may wish to reference the facility or customer name or number here:  <a href=\"https://www15.tceq.texas.gov/crpub/\" rel=\"nofollow\" target=\"_blank\">TCEQ Central Registry Query</a>"
},
{
"id": "124443013",
"answers": [
{
"tag_data": [],
"text": "Hatfield is dumping junk all over the place again!",
"simple_text": "Hatfield is dumping junk all over the place again!"
}
],
"family": "open_ended",
"subtype": "essay",
"heading": "A brief description of the problem:"
},
{
"id": "124443014",
"answers": [
{
"content_type": "image/jpeg",
"tag_data": [],
"text": "waste.jpg",
"download_url": "https://sm-fileupload.s3.amazonaws.com/removed",
"simple_text": "waste.jpg"
}
],
"family": "open_ended",
"subtype": "single",
"heading": "Upload with the <em>Choose File</em> button below."
},
{
"id": "124443022",
"answers": [
{
"choice_id": "933396022",
"simple_text": "Contact me with updates."
}
],
"family": "single_choice",
"subtype": "vertical",
"heading": "Would you like to provide information for us to contact you?"
},
{
"id": "124443021",
"answers": [
{
"choice_id": "933396021",
"simple_text": "No preference"
}
],
"family": "single_choice",
"subtype": "vertical",
"heading": "If you are providing information, what is your preferred way of contact?"
},
{
"id": "124443024",
"answers": [
{
"row_id": "933396027",
"tag_data": [],
"text": "Bess New",
"simple_text": "\n Name\n | Bess New"
},
{
"row_id": "933396029",
"tag_data": [],
"text": "0 No name Road",
"simple_text": "\n Address\n | 0 No name Road"
},
{
"row_id": "933396031",
"tag_data": [],
"text": "Austin",
"simple_text": "\n City/Town\n | Austin"
},
{
"row_id": "933396033",
"tag_data": [],
"text": "78753",
"simple_text": "\n ZIP/Postal Code\n | 78753"
},
{
"row_id": "933396035",
"tag_data": [],
"text": "bess@somedomain.com",
"simple_text": "\n Email Address\n | bess@somedomain.com"
},
{
"row_id": "933396036",
"tag_data": [],
"text": "5555555555",
"simple_text": "\n Phone Number\n | 5555555555"
}
],
"family": "demographic",
"subtype": "international",
"heading": "Contact Information"
}
]
}
]

[grantjenkins]

It looks like you need to use the questions array from your Parse JSON (should be a dynamic property called questions from your Parse JSON). This would be the input into the XML expressions and the Select.

body('Parse_JSON')?['questions']

[Bcnewton]

Sorry, I was away for a day.

Thank you! I really appreciate your time.  So very close now!  I am getting all of the info flowing into the Select and the Parse JSON, however, the only dynamic controls it gives me are ID, Questions, Answers.

If I use any of these, it puts me right back into an "Apply to each" where I cannot get the individual answers.

I am able to do the Apply to each with just a Select and no xml.  But that was not working because I could not get to the individual items.  I thought the xpath and xml would be the thing that got me there, but after all that, I am right back to the Apply to each. And obviously, I just do not know how to get to the individual items that I need.

Can you tell me how to get the individual items?

Here is all I get for dynamic fields, not individual questions and answers: 

[Bcnewton]

Hi@grantjenkins  Not sure if you saw my response above.  Any ideas on how I can achieve the final step of getting individual items out?  Or do I need to start a new question?  Thanks again!