Page Comparison

...

Step 1: Create XML Data in ADF

1. Create a Data Flow:

   • In Azure Data Factory Studio, go to the Author tab.

   • Create a new Data Flow and name it HouseholdCaseXML.

2. Add Source:

   • Select your Azure SQL Database dataset containing household data (hh_sqldata).

   • Ensure columns include household_id, access_to_healthcare_services, availability_of_health_insurance, access_to_clean_drinking_water, and case_id.

3. Add Derived Column Transformation:

   • Name the transformation hhxml.

   • Create a derived column hh_xml using the following expression. This can be easily copied from the Raw XML section of any form submission within CommCare for a household registration.

   • Replace sections where inputs from the SQL DB are required. For example, case id, household_id and others as seen in this example.

   • Ensure that the instance_id has a UUID value for creating a unique ID for the submission.

   Code Block
   language text
   concat( '<?xml version="1.0" ?>', '<data xmlns:jrm="<http://dev.commcarehq.org/jr/xforms>" xmlns="<http://openrosa.org/formdesigner/4E761EC2-20EA-47EB-904E-557041DCAD1C>" uiVersion="1" version="77" name="Household Registration">', ' <household_id>', household_id, '</household_id>', ' <access_to_healthcare_services>', access_to_healthcare_services, '</access_to_healthcare_services>', ' <availability_of_health_insurance>', availability_of_health_insurance, '</availability_of_health_insurance>', ' <access_to_clean_drinking_water>', access_to_clean_drinking_water, '</access_to_clean_drinking_water>', ' <n0:case xmlns:n0="<http://commcarehq.org/case/transaction/v2>" case_id="', lower(case_id), '" date_modified="', currentTimestamp(), '+05:30" user_id="170794ebd70648668c3d8fa7728abfce">', ' <n0:create>', ' <n0:case_name>', household_id, '</n0:case_name>', ' <n0:owner_id>170794ebd70648668c3d8fa7728abfce</n0:owner_id>', ' <n0:case_type>household</n0:case_type>', ' </n0:create>', ' <n0:update>', ' <n0:access_to_clean_drinking_water>', access_to_clean_drinking_water, '</n0:access_to_clean_drinking_water>', ' <n0:access_to_healthcare_services>', access_to_healthcare_services, '</n0:access_to_healthcare_services>', ' <n0:availability_of_health_insurance>', availability_of_health_insurance, '</n0:availability_of_health_insurance>', ' <n0:household_id>', household_id, '</n0:household_id>', ' </n0:update>', ' </n0:case>', ' <n1:meta xmlns:n1="<http://openrosa.org/jr/xforms>">', ' <n1:deviceID>Formplayer</n1:deviceID>', ' <n1:timeStart>', currentTimestamp(), '+05:30</n1:timeStart>', ' <n1:timeEnd>', currentTimestamp(), '+05:30</n1:timeEnd>', ' <n1:username>migration</n1:username>', ' <n1:userID>170794ebd70648668c3d8fa7728abfce</n1:userID>', ' <n1:instanceID>', uuid(), '</n1:instanceID>', ' <n2:appVersion xmlns:n2="<http://commcarehq.org/xforms>">Formplayer Version: 2.53</n2:appVersion>', ' <n1:drift>0</n1:drift>', ' </n1:meta>', '</data>' )

4. Add Sink:

   • Add a sink transformation to save the generated XML (hhxml) to your Azure SQL Database in the hh_submission table under the final_xml column.

5. Publish the Data Flow:

   • Save and publish your Data Flow to make it available for execution.

6. Execute the Data Flow

• Trigger the Data Flow in your pipeline to generate and store the household case XML in the Azure SQL Database.

Step 2: Submit XML Data to CommCare

...

1. Create a New Pipeline:

   • Name it Household Submission.

2. Add Lookup Activity:

   • Add a Lookup activity to retrieve the final_xml data from the hh_submission table.

   • Configure the SQL query as follows:

   Code Block
   language sql
   SELECT final_xml FROM hh_submission;

3. Add Get Credentials Activity:

   • Use a Web Activity to retrieve the API key from Azure Key Vault.

   • Set the method to GET and point to the Key Vault secret.

4. Add ForEach Activity:

   • Add a ForEach activity to iterate over the results of the Lookup activity.

   • In the ForEach activity, add a Web Activity to submit each final_xml to CommCare’s API.

5. Configure Web Activity for Submission:

   • Set the HTTP method to POST.

   • Add a header for Authorization using the output from the GetCredentials activity.

   • Set the body to @item().final_xml to send the XML data.

6. Publish and Execute the Pipeline:

   • Publish the pipeline and run it to submit your household case XMLs to CommCare.