0

I am trying to build a dataset from an xml feed and loop through each record. The Process below will loop through and populate a single record from the XML file, but won't populate anything beyond a single patient. The error that we have identified is in the first set statement after the ENDIF for the loop. When we remove this, the template triggers fine, but the appointment details don't populate in the body. Any ideas/suggestions?

XML

<Root><Record AppointmentDateTime="Sep 24 2015  2:00PM" AppointmentId="26771701" AppointmentStatusCode="10" AppointmentTypeCode="1" ClinicId="14" EmailAddress="FULLERSPECIAL@gmail.com" FamilyId="88690" FirstName="Carnijah" HOHID="230830" InsuranceStatusCode="2" LastName="Horne" PatientId="495350" PayerTypeCode="1" RescheduleYN="Y" /><Record AppointmentDateTime="Sep 24 2015  2:00PM" AppointmentId="26771705" AppointmentStatusCode="10" AppointmentTypeCode="1" ClinicId="14" EmailAddress="FULLERSPECIAL@gmail.com" FamilyId="88690" FirstName="Carmone" HOHID="230830" InsuranceStatusCode="2" LastName="Fuller" PatientId="230835" PayerTypeCode="1" RescheduleYN="Y" /><Record AppointmentDateTime="Sep 24 2015  2:00PM" AppointmentId="26771709" AppointmentStatusCode="10" AppointmentTypeCode="1" ClinicId="14" EmailAddress="FULLERSPECIAL@gmail.com" FamilyId="88690" FirstName="Carshawn" HOHID="230830" InsuranceStatusCode="2" LastName="Fuller" PatientId="230831" PayerTypeCode="1" RescheduleYN="Y" /></Root>

Code

%%[
    var @xml
    set @xml = XML_Feed
    Set @rowset = BuildRowsetFromXml(@xml, "//Record", 1)
    Set @FamilyTotal = Rowcount(@rowset)

    If not empty(@FamilyTotal) then
        for @i = 1 to Rowcount(@rowset) do

        Set @row = Row(@rowset,@i)
        Set @FamilyAppt = Field(@row,"AppointmentId_att")
        Set @ApptSubscriberKey = Field(@row,"PatientId_att")
        Set @familyid = Field(@row,"FamilyId_att")
        Set @HOHid = Field(@row,"HOHID_att")

        If @i == 1 THEN
            Set @familyid = Field(@row,"FamilyId_att")
            Set @FamilyFirstNames = Field(@row,"FirstName_att")
            Set @FamilyApptTime = Field(@row,"AppointmentDateTime_att")
            Set @AppointmentTypeCode = Field(@row,"AppointmentTypeCode_att")
        ENDIF

    Next @i

    Else
    RaiseError("Missing subscriber information.",true)
    ENDIF

    Set @PatientSubKey = Field(@row, "PatientId_att")
    Set @PatientFirstName = Field(@row, "FirstName_att")
    Set @PatientLastName = Field(@row, "LastName_att")
    Set @PatientAppt = Field(@row, "AppointmentId_att")
    Set @PatientApptTime = Field(@row, "AppointmentDateTime_att")
    Set @PatientApptDay = Format(@PatientApptTime,"dddd, MMM, d, hh, mm, tt")
]%%

Body copy that populates patient data:

 %%[
 for @i = 1 to Rowcount(@rowset) do

       Set @row = Row(@rowset,@i)
       Set @PatientSubKey = Field(@row,"PatientId_att")
       Set @HoHid = Field(@row,"HOHID_att")
       Set @PatientFirstName = Propercase(Field(@row,"FirstName_att"))
       Set @PatientLastName = Field(@row,"LastName_att")
       Set @AppointmentTypeCode = Field(@row,"AppointmentTypeCode_att")
       Set @PatientAppt = Field(@row, "AppointmentId_att")
       Set @PatientApptTime = Field(@row,"AppointmentDateTime_att")
       Set @PatientApptDay = Format(@PatientApptTime,"dddd, MMM, d, hh, mm, tt")       Set @familyid = Field(@row,"FamilyId_att")
       Set @RescheduleYN = Field(@row,"RescheduleYN_att")

                    INSERTDE(
                    "Patient_Records_DB",
                    "PatientID",@PatientSubKey,
                    "HOHid",@HoHid,
                    "PatientFirstName",@PatientFirstName,
                    "FamilyID",@familyid,
                    "AppointmentID",@PatientAppt,
                    "AppointmentDateTime",@PatientApptTime,
                    "AppointmentTypeCode",@AppointmentTypeCode,
                    "RescheduleYN",@RescheduleYN,
                    "EventDate",@EventDate)

               ]%%
                        <h4 class="center" style="text-align: center; color: #0287bf; font-family: 'Helvetica', 'Arial', sans-serif; font-weight: bold; line-height: 1.3; word-break: normal; font-size: 18px; margin: 0; padding: 0;" align="center">
                          %%= v(@PatientFirstName) =%%'s appointment
                        </h4>
                        <p class="center" style="text-align: center; color: #222222; font-family: 'Helvetica', 'Arial', sans-serif; font-weight: normal; line-height: 19px; font-size: 14px; margin: 0 0 10px; padding: 0;" align="center">We'll see you at %%= v(Lowercase(Format(@PatientApptTime,"h:mm tt"))) =%% on %%= v(Format(@PatientApptTime,"dddd")) =%%, %%= v(Format(@PatientApptTime,"MMM")) =%% %%= v(Format(@PatientApptTime,"dd")) =%%.</p>
                        %%[ If @FamilyTotal > 1 THEN ]%%
                        <br />
                        <br />
                        %%[ ENDIF ]%%  
                        %%[ next @i ]%%
2
  • It's not real clear what you're trying to do with the code in your question. There are several fields not defined -- @cntr, @eventDate, etc. – Adam Spriggs Sep 28 '15 at 14:11
  • cntr should be i and event date is now – Realrx7 Sep 28 '15 at 14:12
1
+50

Appears your XML isn't valid. Missing an ending > before </record> also the EmailAddress can't contain < or >.

I ran it through the Free Online XML Formatter as a test.

Update:

Perhaps this will help. I fixed the syntax issues with the XML and got it to output the info:

%%[

  var @xml
  set @xml = '<Root><Record AppointmentDateTime="Sep 24 2015  2:00PM" AppointmentId="26771701" AppointmentStatusCode="10" AppointmentTypeCode="1" ClinicId="14" EmailAddress="FULLERSPECIAL@gmail.com" FamilyId="88690" FirstName="Carnijah" HOHID="230830" InsuranceStatusCode="2" LastName="Horne" PatientId="495350" PayerTypeCode="1" RescheduleYN="Y" /><Record AppointmentDateTime="Sep 24 2015  2:00PM" AppointmentId="26771705" AppointmentStatusCode="10" AppointmentTypeCode="1" ClinicId="14" EmailAddress="FULLERSPECIAL@gmail.com" FamilyId="88690" FirstName="Carmone" HOHID="230830" InsuranceStatusCode="2" LastName="Fuller" PatientId="230835" PayerTypeCode="1" RescheduleYN="Y" /><Record AppointmentDateTime="Sep 24 2015  2:00PM" AppointmentId="26771709" AppointmentStatusCode="10" AppointmentTypeCode="1" ClinicId="14" EmailAddress="FULLERSPECIAL@gmail.com" FamilyId="88690" FirstName="Carshawn" HOHID="230830" InsuranceStatusCode="2" LastName="Fuller" PatientId="230831" PayerTypeCode="1" RescheduleYN="Y" /></Root>'

  Set @rowset = BuildRowsetFromXml(@xml, "//Record", 1)
  Set @FamilyTotal = Rowcount(@rowset)

  If not empty(@FamilyTotal) then

    for @i = 1 to Rowcount(@rowset) do

      Set @row = Row(@rowset,@i)

      If @i == 1 THEN
          Set @familyid = Field(@row,"FamilyId_att")
          Set @FamilyFirstNames = Field(@row,"FirstName_att")
          Set @FamilyApptTime = Field(@row,"AppointmentDateTime_att")
          Set @AppointmentTypeCode = Field(@row,"AppointmentTypeCode_att")
          outputline(concat("<br><br>familyid: ",@familyid))
          outputline(concat("<br>FamilyFirstNames: ",@FamilyFirstNames))
          outputline(concat("<br>FamilyApptTime: ",@FamilyApptTime))
          outputline(concat("<br>AppointmentTypeCode: ",@AppointmentTypeCode))
      ENDIF

      Set @FamilyAppt = Field(@row,"AppointmentId_att")
      Set @ApptSubscriberKey = Field(@row,"PatientId_att")
      Set @HOHid = Field(@row,"HOHID_att")
      Set @PatientSubKey = Field(@row, "PatientId_att")
      Set @PatientFirstName = Field(@row, "FirstName_att")
      Set @PatientLastName = Field(@row, "LastName_att")
      Set @PatientAppt = Field(@row, "AppointmentId_att")
      Set @PatientApptTime = Field(@row, "AppointmentDateTime_att")
      Set @PatientApptDay = Format(@PatientApptTime,"dddd, MMM, d, hh, mm, tt")
      Set @RescheduleYN = Field(@row,"RescheduleYN_att")

      outputline(concat("<br><br>&nbsp;FamilyAppt: ",@FamilyAppt))
      outputline(concat("<br>&nbsp;ApptSubscriberKey: ",@ApptSubscriberKey))
      outputline(concat("<br>&nbsp;HOHid: ",@HOHid))
      outputline(concat("<br>&nbsp;PatientSubKey: ",@PatientSubKey))
      outputline(concat("<br>&nbsp;PatientFirstName: ",@PatientFirstName))
      outputline(concat("<br>&nbsp;PatientLastName: ",@PatientLastName))
      outputline(concat("<br>&nbsp;PatientAppt: ",@PatientAppt))
      outputline(concat("<br>&nbsp;FamilyAppt: ",@FamilyAppt))
      outputline(concat("<br>&nbsp;PatientApptTime: ",@PatientApptTime))
      outputline(concat("<br>&nbsp;PatientApptDay: ",@PatientApptDay))
      outputline(concat("<br>&nbsp;RescheduleYN: ",@RescheduleYN))

      /* insertDE() here */

      ]%%
      <h4>%%=v(@PatientFirstName)=%%'s appointment</h4>
      <p>We'll see you at %%=Lowercase(Format(@PatientApptTime,"h:mm tt"))=%%
      on %%=Format(@PatientApptTime,"dddd")=%%, %%=Format(@PatientApptTime,"MMM")=%% %%=Format(@PatientApptTime,"dd")=%%.
      </p>
      %%[

    Next @i

  Else
    RaiseError("Missing subscriber information.",true)
  ENDIF

]%%

Output:

familyid: 88690 
FamilyFirstNames: Carnijah 
FamilyApptTime: Sep 24 2015 2:00PM 
AppointmentTypeCode: 1 

 FamilyAppt: 26771701 
 ApptSubscriberKey: 495350 
 HOHid: 230830 
 PatientSubKey: 495350 
 PatientFirstName: Carnijah 
 PatientLastName: Horne 
 PatientAppt: 26771701 
 FamilyAppt: 26771701 
 PatientApptTime: Sep 24 2015 2:00PM 
 PatientApptDay: Thursday, Sep, 24, 02, 00, PM 
 RescheduleYN: Y

Carnijah's appointment

We'll see you at 2:00 pm on Thursday, Sep 24.



 FamilyAppt: 26771705 
 ApptSubscriberKey: 230835 
 HOHid: 230830 
 PatientSubKey: 230835 
 PatientFirstName: Carmone 
 PatientLastName: Fuller 
 PatientAppt: 26771705 
 FamilyAppt: 26771705 
 PatientApptTime: Sep 24 2015 2:00PM 
 PatientApptDay: Thursday, Sep, 24, 02, 00, PM 
 RescheduleYN: Y

Carmone's appointment

We'll see you at 2:00 pm on Thursday, Sep 24.



 FamilyAppt: 26771709 
 ApptSubscriberKey: 230831 
 HOHid: 230830 
 PatientSubKey: 230831 
 PatientFirstName: Carshawn 
 PatientLastName: Fuller 
 PatientAppt: 26771709 
 FamilyAppt: 26771709 
 PatientApptTime: Sep 24 2015 2:00PM 
 PatientApptDay: Thursday, Sep, 24, 02, 00, PM 
 RescheduleYN: Y

Carshawn's appointment

We'll see you at 2:00 pm on Thursday, Sep 24.
6
  • Both updates have been added, but still the same result. – Realrx7 Sep 25 '15 at 16:39
  • Can a rowset be built more than once in a single template trigger? – Realrx7 Sep 28 '15 at 13:48
  • Yes. you can build as many rowsets as you need. Your XML is still invalid, BTW. – Adam Spriggs Sep 28 '15 at 13:53
  • Looks like we are having an issue with the XML feed itself. When we hard code the file it works fine, but when we just do a print out of the xml variable, it is blank. – Realrx7 Sep 28 '15 at 19:55
  • Print out is being done once we set the xml variable to XML_Feed – Realrx7 Sep 28 '15 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.