Using AMPscript to pull XML from DE

Question

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!

Answer 1

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

• buildrowsetfromxml
• GitHub Gist - Parse ASC XML Payload