0

I'm trying to create an email that pulls XML from a field in a data extension but my ampscript knowledge is very limited.

The DE field that the XML is under is a text field called 'attribute' and here is the XML file:

<abandonedcart>
   <orderitems>
      <item>
         <itemid_att>1</itemid_att>
         <quantity_att>1</quantity_att>
         <totalprice_att>10.99</totalprice_att>
         <imageurl_att>https:\\image1.img</imageurl_att>
         <type_att>template</type_att>
         <size_att>12x8</size_att>
         <material_att>Corro</material_att>
         <ppi_att>10.99</ppi_att>
      </item>
      <item>
         <itemid_att>2</itemid_att>
         <quantity_att>2</quantity_att>
         <totalprice_att>20.18</totalprice_att>
         <imageurl_att>https:\\image2.img</imageurl_att>
         <type_att>template</type_att>
         <size_att>12x8</size_att>
         <material_att>Corro</material_att>
         <ppi_att>10.09</ppi_att>
      </item>
   </orderitems>
</abandonedcart>

I know that I need to use the BuildRowsetFromXml function but I'm getting varying suggestions from different sources and would love some advice!

4

I'd recommend looking over how XPATH 1.0 works -- that's the key part to understanding how to pull node attributes and values from the payload.

Here's an example with a different, but similar payload.

<cart>
      <items>
            <item>
                  <sku><![CDATA[123]]></sku>
                  <name><![CDATA[Square]]></name>
                  <url><![CDATA[https://example.com?sku=123]]></url>
            </item>
            <item>
                  <sku><![CDATA[456]]></sku>
                  <name><![CDATA[Circle]]></name>
                  <url><![CDATA[https://example.com?sku=456]]></url>
            </item>
            <item>
                  <sku><![CDATA[789]]></sku>
                  <name><![CDATA[Triangle]]></name>
                  <url><![CDATA[https://example.com?sku=789]]></url>
            </item>
      </items>
</cart>
%%[

/* Sample XML Payload: https://gist.github.com/wvpv/8bec8dc8774fdd1fedb25a69071ac905 */

var @xml, @nodes, @rowCount
set @xml = AttributeValue("xml")

if indexOf(@xml,"<cart>") > 0 then

  set @nodes = BuildRowsetFromXML(@xml,"/cart/items/item",0)
  set @rowCount = rowcount(@nodes)

  if @rowCount > 0 then

    for @i = 1 to @rowCount do

      var @nodepath
      var @sku
      var @name
      var @url

      set @nodepath = concat("/cart/items/item[",@i,"]/")

      if rowcount(BuildRowsetFromXML(@xml,concat(@nodepath,"sku"))) > 0 then
          set @sku = Field(Row(BuildRowsetFromXML(@xml,concat(@nodepath,"sku"),0),1),'Value')
      endif

      if rowcount(BuildRowsetFromXML(@xml,concat(@nodepath,"name"))) > 0 then
          set @name = Field(Row(BuildRowsetFromXML(@xml,concat(@nodepath,"name"),0),1),'Value')
      endif

      if rowcount(BuildRowsetFromXML(@xml,concat(@nodepath,"url"))) > 0 then
          set @url = Field(Row(BuildRowsetFromXML(@xml,concat(@nodepath,"url"),0),1),'Value')
      endif

      if not empty(@sku) and not empty(@name) and not empty(@url) then

      ]%%

       <br><a href="%%=redirectto(@url)=%%">%%=v(@sku)=%%</a> - <a href="%%=redirectto(@url)=%%">%%=v(@name)=%%</a>

      %%[

      endif

    next @i

  else

   outputline(concat("<br>no products found"))

  endif

else

  outputline(concat("<br>no XML found"))

endif

]%%

Reference

| improve this answer | |
  • A good answer. Depending on whether you're doing much manipulation of the XML data or just wanting to present the data in your email, you could also consider TransformXML as an alternative - developer.salesforce.com/docs/…. It can prove to be a little more maintainable than using AMPScript to manipulate and present XML based data. – Macca May 7 at 2:38

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.