How to pull child content using AMPscript from XML variable

Question

We are using a ET data extension where one of the fields stores an XML file from our external API. We have to extract the values and parse them to assign for each parent/child contained within the variable. How can we build the rowset for each parent and child while continuing to loop through each value? Here's the file structure and current build:

DECLARE @xml xml
SET @xml =
'<Schema>
<Message>
  <HOH>
        <HOHID>49</HOHID>
        <LastName>Jones</LastName>
        <FirstName>William</FirstName>
        <ContactType>EMAIL</ContactType>
        <ContactValue>[email protected]</ContactValue>
  </HOH>

  <Patient>
        <PatientId>55</PatientId>
        <LastName>Jones</LastName>
        <FirstName>Samuel</FirstName>
        <AppointmentDateTime>12/5/2014 10:00:00</AppointmentDateTime>
        <ClinicId>41</ClinicId>
        <AppointmentTypeCode>1</AppointmentTypeCode>
        <AppointmentSubTypeCode></AppointmentSubTypeCode>
  </Patient>

  <Patient>
        <PatientId>66</PatientId>
        <LastName>Jones</LastName>
        <FirstName>Allison</FirstName>
        <AppointmentDateTime>12/5/2014 10:45:00</AppointmentDateTime>
        <ClinicId>41</ClinicId>
        <AppointmentTypeCode>1</AppointmentTypeCode>
        <AppointmentSubTypeCode></AppointmentSubTypeCode>
  </Patient>
 </Message>

 set @rowset = BuildRowsetFromXml(@xml, "//HOH", 1)
 set @HOHId = Field(Row(@rowset, 1), 'HOHID')

Answer 1

I'm still pretty new to AMPscript but I ran across this tutorial that "might" help you answer your question. The tutorial is based on parsing and building rowsets from an XML feed. You might be able to reverse-engineer it. I hope it helps and good luck.

XML Tutorial:

https://help.exacttarget.com/en-US/documentation/exacttarget/content/ampscript/how_to_include_an_rss_feed_in_an_email_message_using_ampscript/