0

I have an XML file that is listing products multiple times instead of grouping them and saying the quantity is the amount that is listed in the file. For example Magazine Gold is listed 3 times, so in my output I only want to list it once and update the quantity to say 3. Then for the next product if its listed to time, I want the product name and quantity to reflect the actual quantity.

How do I group by the product name and calculate the total using AMPScript?

Here's an example of the XML:

"<?xml version=""1.0"" encoding=""utf-16""?>
<ArrayOfProducts xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
  <Products>
    <Name>MAGAZINE GOLD</Name>
    <ImageUrl>Images/MagazineGoldCard-small.png</ImageUrl>
    <Price>2.99</Price>
    <Quantity>1</Quantity>
    <ProductType>GiftCard</ProductType>
    <MonthlyTotal>2.99</MonthlyTotal>
    <StartDate>9/23/2015</StartDate>
    <NextBillingDate />
  </Products>
</ArrayOfProducts><?xml version=""1.0"" encoding=""utf-16""?>
<ArrayOfProducts xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
  <Products>
    <Name>MAGAZINE GOLD</Name>
    <ImageUrl>Images/MagazineGoldCard-small.png</ImageUrl>
    <Price>2.99</Price>
    <Quantity>1</Quantity>
    <ProductType>GiftCard</ProductType>
    <MonthlyTotal>2.99</MonthlyTotal>
    <StartDate>9/23/2015</StartDate>
    <NextBillingDate />
  </Products>
</ArrayOfProducts><?xml version=""1.0"" encoding=""utf-16""?>
<ArrayOfProducts xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
  <Products>
    <Name>MAGAZINE GOLD</Name>
    <ImageUrl>Images/MagazineGoldCard-small.png</ImageUrl>
    <Price>2.99</Price>
    <Quantity>1</Quantity>
    <ProductType>GiftCard</ProductType>

    <MonthlyTotal>2.99</MonthlyTotal>
    <StartDate>9/23/2015</StartDate>
    <NextBillingDate />
  </Products>
</ArrayOfProducts><?xml version=""1.0"" encoding=""utf-16""?>
<ArrayOfProducts xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
  <Products>
    <Name>PHONE PROTECTION PLAN</Name>
    <ImageUrl>Images/All_Phone_Protection_Plan-small.png</ImageUrl>
    <Price>5.99</Price>
    <Quantity>1</Quantity>
    <ProductType>Insurance_Phone</ProductType>

    <MonthlyTotal>5.99</MonthlyTotal>
    <StartDate>9/22/2015</StartDate>
    <NextBillingDate />
  </Products>
</ArrayOfProducts><?xml version=""1.0"" encoding=""utf-16""?>
<ArrayOfProducts xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
  <Products>
    <Name>PHONE PROTECTION PLAN</Name>
    <ImageUrl>Images/All_Phone_Protection_Plan-small.png</ImageUrl>
    <Price>5.99</Price>
    <Quantity>1</Quantity>
    <ProductType>Insurance_Phone</ProductType>

    <MonthlyTotal>5.99</MonthlyTotal>
    <StartDate>9/22/2015</StartDate>
    <NextBillingDate />
  </Products>
</ArrayOfProducts><?xml version=""1.0"" encoding=""utf-16""?>
<ArrayOfProducts xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
  <Products>
    <Name>SAMSUNG PROTECTION PLAN</Name>
    <ImageUrl>Images/Samsung_Samsung_Touchscreens-small.png</ImageUrl>
    <Price>6.99</Price>
    <Quantity>1</Quantity>
    <ProductType>Insurance_Phone</ProductType>

    <MonthlyTotal>6.99</MonthlyTotal>
    <StartDate>9/22/2015</StartDate>
    <NextBillingDate />
  </Products>
</ArrayOfProducts><?xml version=""1.0"" encoding=""utf-16""?>
<ArrayOfProducts xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
  <Products>
    <Name>MAGAZINE GOLD</Name>
    <ImageUrl>Images/MagazineGoldCard-small.png</ImageUrl>
    <Price>2.99</Price>
    <Quantity>1</Quantity>
    <ProductType>GiftCard</ProductType>

    <MonthlyTotal>2.99</MonthlyTotal>
    <StartDate>9/23/2015</StartDate>
    <NextBillingDate />
  </Products>
</ArrayOfProducts><?xml version=""1.0"" encoding=""utf-16""?>
<ArrayOfProducts xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">
  <Products>
    <Name>TABLET PROTECTION PLAN</Name>
    <ImageUrl>Images/All_Tablet_Small.png</ImageUrl>
    <Price>5.99</Price>
    <Quantity>1</Quantity>
    <ProductType>Insurance_Tablet</ProductType>

    <MonthlyTotal>5.99</MonthlyTotal>
    <StartDate>9/22/2015</StartDate>
    <NextBillingDate />
  </Products>
</ArrayOfProducts>"

The current ampscript is:

%%[                                           
Set @ProductInfoXML = Product_Info
Set @ProductInfoXML = Replace(@ProductInfoXML,'<?xml version="1.0" encoding="utf-16"?>','')
Set @ProductInfoXML = ConCat('<?xml version="1.0" encoding="utf-16"?><root>',@ProductInfoXML,'</root>')
Set @rowsetArrayOfProducts = BuildRowsetFromXML(@ProductInfoXML, '//ArrayOfProducts', 1)
Set @rowsetProductName = BuildRowsetFromXML(@ProductInfoXML, '//Name', 1)
Set @rowsetProductImage = BuildRowsetFromXML(@ProductInfoXML, '//ImageUrl', 1)
Set @rowsetProductQuantity = BuildRowsetFromXML(@ProductInfoXML, '//Quantity', 1)
Set @rowsetProductPrice = BuildRowsetFromXML(@ProductInfoXML, '//Price', 1)
Set @rowsetProductType = BuildRowsetFromXML(@ProductInfoXML, '//ProductType', 1)
Set @rowsetProductStartDate = BuildRowsetFromXML(@ProductInfoXML, '//StartDate', 1)


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


if (Rowcount(@rowsetProductName) >= @i) then
    Set @Product_Name = Field(Row(@rowsetProductName,@i),1) 
  else 
    set @Product_Name = @rowsetProductName
  endif

if (Rowcount(@rowsetProductImage) >= @i) then
    Set @Product_Image = Field(Row(@rowsetProductImage,@i),1) 
  else 
    set @Product_Image = @rowsetProductImage
  endif

if (Rowcount(@rowsetProductQuantity) >= @i) then
    Set @Product_Quantity = Field(Row(@rowsetProductQuantity,@i),1) 
  else 
    set @Product_Quantity = @rowsetProductQuantity
  endif

  if (Rowcount(@rowsetProductPrice) >= @i) then
    Set @Product_Price = Field(Row(@rowsetProductPrice,@i),1) 
  else 
    set @Product_Price = @rowsetProductPrice
  endif

  if (Rowcount(@rowsetProductType) >= @i) then
    Set @Product_Type = Field(Row(@rowsetProductType,@i),1) 
  else 
    set @Product_Type = @rowsetProductType
  endif

  if (Rowcount(@rowsetProductStartDate) >= @i) then
    Set @Product_StartDate = Field(Row(@rowsetProductStartDate,@i),1) 
  else 
    set @Product_StartDate = @rowsetProductStartDate
  endif

  ]%%

<br>&#149; %%=ProperCase(@Product_Name)=%%
                                                <br>&#149; <strong>Price:</strong> $%%=v(@Product_Price)=%%
                                                <br>&#149; <strong>Quantity:</strong> %%=v(@Product_Quantity)=%%
                                                <br>&#149; <strong>Start Date:</strong> %%=v(@Product_StartDate)=%%

%%[next @i]%%
0

Here's an example I generalized from a project I'm working on. The key is to reference nodes by ordinal value in your for loop. Inside your for loop, you can just use the Add() function to accumulate values as you iterate.

%%[

set @lineItemsXML = AttributeValue("lineItemsXML")

var @i, @maxSKUsToDisplay, @nodes, @rowCount

set @nodes = BuildRowsetFromXML(@lineItemsXML,"/node1/items/",0)
set @rowCount = rowcount(@nodes)

set @maxSKUsToDisplay = 3

set @rowCount = iif(@rowCount > @maxSKUsToDisplay, @maxSKUsToDisplay, @rowCount)

if @rowCount > 0 then

  for @i = 1 to @rowCount do

    var @skuName
    var @sku

    set @skuName = Field(Row(BuildRowsetFromXML(@lineItemsXML,concat("/node1/items[",@i,"]/name"),0),1),'Value')
    set @sku = Field(Row(BuildRowsetFromXML(@lineItemsXML,concat("/node1/items[",@i,"]/sku"),0),1),'Value')

   if not empty(@skuName) then

   ]%%

    <br>%%=v(@skuName)=%%
    <br>%%=v(@sku)=%%

   %%[ endif ]%%

%%[ next @i ]%%

%%[ else ]%%

%%[ raiseerror("no products found") ]%%

%%[ endif ]%%
  • I am still a bit confused. I have the product Magazine Gold and its listed 3 times in the XML, but I want my output to only list Magazine Gold once and say the quantity is 3 and if they have another product with multiple purchases I want to list them once and display the quantity. I have the add down. It's just limiting the listing to one output that I am getting confused. Thank you! – gcardona Sep 24 '15 at 15:39
  • If your ArrayOfProducts was sorted by \ArrayOfProducts\Products\Name, then a simple loop and total the Quantity and then outputting when the Name varies would be a good solution. But since it's not, you're stuck with some XPATH gymnastics. – Adam Spriggs Sep 25 '15 at 14:38
  • If it's helpful, you can retrieve the unique product names using this: set @rowSetUniqueProductNames = BuildRowSetFromXML(@ProductInfoXML, '//ArrayOfProducts/Products/Name[not(preceding::Products/Name/. = .)]', 1) – Adam Spriggs Sep 25 '15 at 14:39
  • My suggestion would be to use an XSLT and the TransformXML() function, where you have a lot more flexibility to do things like grouping and sorting. SFMC is limited to XPATH 1.0, so keep that in mind. – Adam Spriggs Sep 25 '15 at 14:45

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.