Versions Compared

Old Version 2

changes.mady.by.user Rishabh Rath

Saved on

compared with

New Version Current

changes.mady.by.user Rishabh Rath

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  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
    languagetext
    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

...