How to pull into content from XML using AMPscript?

Question

I have a data extension in ExactTarget where in one of the fields we have XML code from where I have to extract specific info to print it out in an email newsletter.

E.g.:

FirstName: James
Last Name: Smith
XML: <root><Item count='97' member_id='123456' priority='1' type='CarIDrive' ></Item><Item count='97' member_id='7890123' priority='2' type='PlacesILike' ></Item><Item count='69' member_id='765432378' priority='3' type='GasPerMile' ></Item></root>

So the excel data files has the above headers: FirstName LastName XML

In the HTML email we will have a couple of places where the copy will be populated dynamically through AMPscript. e.g. FirstName, LastName, CarIDrive, PlacesILike and GasPerMile.

How can I assign variables to CarIDrive, PlacesILike and GasPerMile from the XML code from the data extension so I can call these variables in my HTML email?

Thanks, Vic

Answer 1

Use the BuildRowSetFromXML(S1,S2,B1)

Given the XML

<root>
  <Item count='97' member_id='123456' priority='1' type='CarIDrive' ></Item>
  <Item count='97' member_id='7890123' priority='2' type='PlacesILike' ></Item>
  <Item count='69' member_id='765432378' priority='3' type='GasPerMile' ></Item>
</root>

You would do this:

set @rowset = BuildRowsetFromXml(@xml, "//Item", 1)

Resulting in the rowset:

Value    XML    member_id_att   priority_att  type_att
 ""      ""     123456          1             CarIDrive
 ""      ""     7890123         1             PlacesILike
 ""      ""     765432378       1             GasPerMile

Access the attributes like normal, or loop through etc:

set @member_id = Field(Row(@rowset, 1), 'member_id_att')