{"type":"doc","content":[{"type":"paragraph","content":[{"text":"This tutorial provides a comprehensive walkthrough on creating and submitting household member cases XML for CommCare using Azure Data Factory (ADF). The process entails generating XML data in ADF, storing it in an Azure SQL Database, and subsequently posting it to CommCare’s API.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 1: Create Household Member XML Data in ADF","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"full-width","width":1800,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1523,"id":"4a5d8e4e-b88f-4278-aefe-cff8d83d12d8","collection":"contentId-2545877048","type":"file","height":111},"marks":[{"type":"border","attrs":{"size":2,"color":"#091e4224"}}]},{"type":"caption","content":[{"text":"Data Flow for Building Child Case Block XML for Child Cases","type":"text"}]}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a Data Flow","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"In Azure Data Factory Studio, go to the ","type":"text"},{"text":"Author","type":"text","marks":[{"type":"strong"}]},{"text":" tab.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a new Data Flow and name it ","type":"text"},{"text":"HouseholdMemberXML","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add Source","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Select your Azure SQL Database dataset containing household member data (","type":"text"},{"text":"hm_sqldata","type":"text","marks":[{"type":"code"}]},{"text":").","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Ensure columns include ","type":"text"},{"text":"Household_ID","type":"text","marks":[{"type":"code"}]},{"text":", ","type":"text"},{"text":"Member_ID","type":"text","marks":[{"type":"code"}]},{"text":", ","type":"text"},{"text":"Name","type":"text","marks":[{"type":"code"}]},{"text":", ","type":"text"},{"text":"Age","type":"text","marks":[{"type":"code"}]},{"text":", ","type":"text"},{"text":"Gender","type":"text","marks":[{"type":"code"}]},{"text":", ","type":"text"},{"text":"Relation_to_Head","type":"text","marks":[{"type":"code"}]},{"text":", ","type":"text"},{"text":"case_id","type":"text","marks":[{"type":"code"}]},{"text":", and ","type":"text"},{"text":"parent_case_id","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add Derived Column Transformation for Household Member Details Block","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name the transformation ","type":"text"},{"text":"EditHouseholdMemberXMLBlock","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a derived column ","type":"text"},{"text":"hm_xml_snippet","type":"text","marks":[{"type":"code"}]},{"text":" using the following expression. This can be easily copied from the Raw XML section of any form submission within CommCare for a household member registration. ","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"concat(\n ' ',\n ' \" case_id=\"', lower(case_id), '\" date_modified=\"', $formatted_datetime, '\" user_id=\"', $mapped_userid, '\">',\n ' ',\n ' ', Name, '',\n ' ', $mapped_ownerid, '',\n ' household_member',\n ' ',\n ' ',\n ' ', toString(Age), '',\n ' ', Gender, '',\n ' ', Member_ID, '',\n ' ', Relation_to_Head, '',\n ' ',\n ' ',\n ' ', lower(parent_case_id), '',\n ' ',\n ' ',\n ' ', Member_ID, '',\n ' ', Name, '',\n ' ', toString(Age), '',\n ' ', Gender, '',\n ' ', Relation_to_Head, '',\n ' '\n)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add Aggregate Transformation","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name the transformation ","type":"text"},{"text":"AggregatebyParentCaseID","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Aggregate by ","type":"text"},{"text":"parent_case_id","type":"text","marks":[{"type":"code"}]},{"text":" to group household members under their respective parents.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"collect()","type":"text","marks":[{"type":"code"}]},{"text":" to gather all ","type":"text"},{"text":"hm_xml_snippet","type":"text","marks":[{"type":"code"}]},{"text":" into an array.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Convert Array to String","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a derived column to convert the aggregated array into a string and clean up the formatting.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use the following expression:","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"replace(\n replace(\n replace(\n replace(hm_xml_string, '[\"', ''),\n '\"]', ''),\n '\\\\\\\\\\\\\\\\n', ''),\n'\\\\\\\\', '')","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add Sink","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a sink transformation to save the generated XML to your Azure SQL Database in the ","type":"text"},{"text":"hm_submission","type":"text","marks":[{"type":"code"}]},{"text":" table under the ","type":"text"},{"text":"child_xml_block","type":"text","marks":[{"type":"code"}]},{"text":" column.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Publish the Data Flow","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Save and publish your Data Flow to make it available for execution.","type":"text"}]}]}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"1.2 Execute the Data Flow","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Trigger the Data Flow in your pipeline to generate and store the household member case XML in the Azure SQL Database.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 2: Combine with Parent XML and Finalize Submission","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"full-width","width":1800,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1609,"id":"f68cfde7-a106-4e2d-9638-4ad4979f6a6f","collection":"contentId-2545877048","type":"file","height":175}},{"type":"caption","content":[{"text":"Data Flow for Building Parent Case Block XML for Child Cases","type":"text"}]}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a Data Flow","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"In Azure Data Factory Studio, go to the ","type":"text"},{"text":"Author","type":"text","marks":[{"type":"strong"}]},{"text":" tab.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a new Data Flow and name it ","type":"text"},{"text":"ParentCaseBlock","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a Source Transformation","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name the source ","type":"text"},{"text":"HouseholdMembers","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use your Azure SQL Database dataset (","type":"text"},{"text":"hm_sqldata","type":"text","marks":[{"type":"code"}]},{"text":") as the source.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Ensure the following columns are available:","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Household_ID, Member_ID, Name, Age, Gender, Relation_to_Head, case_id, parent_case_id","type":"text","marks":[{"type":"code"}]},{"text":" (or any other relevant fields)","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a Window Transformation","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name it ","type":"text"},{"text":"SplitNodes","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This transformation will generate a row number for each household member under the same parent case, which is useful for uniquely identifying each XML block.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Configure the window to partition by ","type":"text"},{"text":"parent_case_id","type":"text","marks":[{"type":"code"}]},{"text":" and order by ","type":"text"},{"text":"Member_ID","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"window(\n over(parent_case_id),\n asc(Member_ID, true),\n node = rowNumber()-1\n)","type":"text"}]},{"type":"orderedList","attrs":{"order":4},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add an Aggregate Transformation","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name it ","type":"text"},{"text":"AggregateByParent","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Aggregate the data by ","type":"text"},{"text":"parent_case_id","type":"text","marks":[{"type":"code"}]},{"text":" to find the maximum node number for each parent case.","type":"text"}]}]}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"aggregate(\n groupBy(parent_case_id),\n max_node = max(node)\n)","type":"text"}]},{"type":"orderedList","attrs":{"order":5},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Join Aggregated Data with Source","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name this transformation ","type":"text"},{"text":"AggregateCaseNodes","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Join the output of ","type":"text"},{"text":"SplitNodes","type":"text","marks":[{"type":"code"}]},{"text":" with ","type":"text"},{"text":"AggregateByParent","type":"text","marks":[{"type":"code"}]},{"text":" on the ","type":"text"},{"text":"parent_case_id","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This will allow you to create a parent case XML block that contains all related household members.","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"join(\n HouseholdMembers@parent_case_id == AggregateByParent@parent_case_id,\n joinType: 'left',\n matchType: 'exact'\n)","type":"text"}]},{"type":"orderedList","attrs":{"order":6},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a Filter Transformation","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name it ","type":"text"},{"text":"OnlyParentCases","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Filter the rows to isolate the maximum node value for each parent case.","type":"text"}]}]}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"filter(node == max_node)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 2: Create Parent Case XML Block","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a Derived Column Transformation","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name it ","type":"text"},{"text":"ParentCaseXMLBlock","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use this transformation to generate the XML for the parent case block.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The derived column should be named ","type":"text"},{"text":"parent_case_xmlblock","type":"text","marks":[{"type":"code"}]},{"text":" and use the following expression:","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"concat(\n '\" case_id=\"', lower(AggregateByParent@parent_case_id), '\" date_modified=\"', $formatted_datetime, '\" user_id=\"', $mapped_userid, '\"/>'\n)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Finalize Parent Case XML","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add another derived column transformation named ","type":"text"},{"text":"FinalParentCaseXMLBlock","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This transformation will ensure that the node value in the XML is unique by replacing placeholders with the correct node numbers.","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"derive(\n finalparentxmlblock = replace(parent_case_xmlblock, 'n1', concat('n', toString(max_node + 1)))\n)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 3: Generate Footer XML Block","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Derived Column for Footer XML","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a derived column transformation named ","type":"text"},{"text":"FooterXMLBlock","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This block creates the footer XML for the submission.","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"derive(\n xml_footer = replace($xml_footer_hm, 'n3', concat('n', toString(max_node + 3)))\n)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Final Footer XML Block","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a derived column named ","type":"text"},{"text":"FinalFooterXMLBlock","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This will finalize the footer by ensuring all placeholders are replaced correctly.","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"derive(\n xml_footer = replace(xml_footer, 'n2', concat('n', toString(max_node + 2)))\n)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 4: Join and Combine XML Blocks","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Join Transformation","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a join transformation named ","type":"text"},{"text":"JoinwithFooterXML","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Combine the header, parent case, and footer XML blocks to create the final XML document.","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"join(\n CleanUp@parent_case_id == AggregateByParent@parent_case_id,\n joinType: 'outer',\n matchType: 'exact'\n)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Combine Final XML","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a derived column transformation named ","type":"text"},{"text":"FinalParentandFooterXML","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Combine all XML parts into the final submission XML.","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"derive(\n parent_case_xmlandxml_footer = concat(finalparentxmlblock, '\\\\n', xml_footer)\n)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 5: Upsert the Data to SQL Database","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add an Upsert Transformation","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name it ","type":"text"},{"text":"UpsertCondition","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use an ","type":"text"},{"text":"alterRow()","type":"text","marks":[{"type":"code"}]},{"text":" transformation to determine if the row should be upserted based on the ","type":"text"},{"text":"parent_case_id","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"alterRow(\n upsertIf(true())\n)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Configure the Sink","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name the sink transformation ","type":"text"},{"text":"SQLDBSink","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Configure it to upsert the XML data into the ","type":"text"},{"text":"hm_submission","type":"text","marks":[{"type":"code"}]},{"text":" table, updating the ","type":"text"},{"text":"parent_xml_block","type":"text","marks":[{"type":"code"}]},{"text":" column.","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"sink(\n allowSchemaDrift: true,\n validateSchema: false,\n input(\n child_xml_block as string,\n parent_case_id as string,\n parent_xml_block as string,\n header_xml_block as string\n ),\n deletable: false,\n insertable: false,\n updateable: false,\n upsertable: true,\n keys: ['parent_case_id'],\n format: 'table',\n postSQLs: ['UPDATE hm_submission SET final_xml = ISNULL(header_xml_block, \\\\'\\\\') + ISNULL(child_xml_block, \\\\'\\\\') + ISNULL(parent_xml_block, \\\\'\\\\') WHERE final_xml IS NULL OR final_xml = \\\\'\\\\';']\n)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 6: Submit XML Data to CommCare","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":477,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":477,"id":"f99dac84-4cda-4f37-93b9-2c1ab3eaf167","collection":"contentId-2545877048","type":"file","height":116}}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":466,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":555,"id":"0476f6cf-cd21-48df-9731-ecb7725938dc","collection":"contentId-2545877048","type":"file","height":293}},{"type":"caption","content":[{"text":"Pipelines for Data Submission to CommCare","type":"text"}]}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a Pipeline","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Name it ","type":"text"},{"text":"Household Member Submission","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add Lookup Activity","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a Lookup activity to retrieve the ","type":"text"},{"text":"final_xml","type":"text","marks":[{"type":"code"}]},{"text":" data from the ","type":"text"},{"text":"hm_submission","type":"text","marks":[{"type":"code"}]},{"text":" table.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Configure the SQL query as follows:","type":"text"}]}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"SELECT final_xml FROM hm_submission;","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add Get Credentials Activity","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use a Web Activity to retrieve the API key from Azure Key Vault.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Set the method to ","type":"text"},{"text":"GET","type":"text","marks":[{"type":"code"}]},{"text":" and point to the Key Vault secret.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add ForEach Activity","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a ForEach activity to iterate over the results of the Lookup activity.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"In the ForEach activity, add a Web Activity to submit each ","type":"text"},{"text":"final_xml","type":"text","marks":[{"type":"code"}]},{"text":" to CommCare’s Form Submission API.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Configure Web Activity for Submission","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Set the HTTP method to ","type":"text"},{"text":"POST","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Add a header for ","type":"text"},{"text":"Authorization","type":"text","marks":[{"type":"code"}]},{"text":" using the output from the ","type":"text"},{"text":"GetCredentials","type":"text","marks":[{"type":"code"}]},{"text":" activity.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Set the body to ","type":"text"},{"text":"@item().final_xml","type":"text","marks":[{"type":"code"}]},{"text":" to send the XML data.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Publish and Execute the Pipeline","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Publish the pipeline and run it to submit your household member case XMLs to CommCare.","type":"text"}]}]}]}]}]}],"version":1}

Browser not supported