How to sort line items by OrderLineStatus in XML when AMPScript is looping?

Question

How do I show only items with OrderLineStatus as Shipped and @Published as false? Right now no items are being populated.

Here is my xml based AMPScript:

%%[

IF IndexOf(@xml,"<Published>") > 0 THEN

    set @PublishedRowSet = BuildRowsetFromXML(@xml,"//tXML/Message/Order/Invoices/InvoiceDetail/Published",1)
    set @PublishedRows = RowCount(@PublishedRowSet)

    if @PublishedRows > 0 THEN 
        set @Published = Field(Row(BuildRowsetFromXML(@xml,"//tXML/Message/Order/Invoices/InvoiceDetail/Published",1),1),"value")
    else
        set @Published = ""
    endif   

ELSE

    set @Published = ""

ENDIF

/** Order Info Begins**/
IF IndexOf(@xml,"<ItemID>") > 0 THEN 

    set @ItemIDRowSet = BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/ItemID",1)
    set @ItemIDRows = RowCount(@ItemIDRowSet)

    if @ItemIDRows > 0 THEN 
        set @ItemID = Field(Row(BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/ItemID",1),1),"value")
    else
        set @ItemID = ""
    endif   

ELSE

    set @ItemID=''

ENDIF

IF IndexOf(@xml,"<ItemDescription>") > 0 THEN 
    set @ItemDescriptionRowSet = BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/ItemDescription",1)
    set @ItemDescriptionRows = RowCount(@ItemDescriptionRowSet)

    if @ItemDescriptionRows > 0 THEN 
        set @ItemDescription = Field(Row(BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/ItemDescription",1),1),"value")
    else
        set @ItemDescription = ""
    endif

ELSE

    set @ItemDescription=''

ENDIF

IF IndexOf(@xml,"<OrderedQty>") > 0 THEN 

    set @OrderedQtyRowSet = BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/Quantity/OrderedQty",1)
    set @OrderedQtyRows = RowCount(@OrderedQtyRowSet)

    if @OrderedQtyRows > 0 THEN 
        set @OrderedQty = Field(Row(BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/Quantity/OrderedQty",1),1),"value")
    else
        set @OrderedQty = ""
    endif

ELSE

    set @OrderedQty=''

ENDIF

IF IndexOf(@xml,"<ExtendedPrice>") > 0 THEN 

    set @ExtendedPriceRowSet = BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/PriceInfo/ExtendedPrice",1)
    set @ExtendedPriceRows = RowCount(@ExtendedPriceRowSet)

    if @ExtendedPriceRows > 0 THEN 
        set @ExtendedPrice = Field(Row(BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/PriceInfo/ExtendedPrice",1),1),"value")
    else
        set @ExtendedPrice = ""
    endif       

ELSE

    set @ExtendedPrice=''

ENDIF

/** Order Info  Ends**/

IF IndexOf(@xml,"<OrderLine>") > 0 THEN

    set @OrderLineRowSet = BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/LineNumber",1)    
    set @OrderLineStatusRowSet = BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/OrderLineStatus",1) 

    if @OrderLineStatusRowSet > 0 THEN
        set @OrderLineStatus = Field(Row(BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/OrderLineStatus",1),1),"value")
        set @OrderLine = Field(Row(BuildRowsetFromXML(@xml,"//tXML/Message/Order/OrderLines/OrderLine/LineNumber",1),1),"i")            
    else

    endif

ELSE

ENDIF                                                           

IF @OrderLineStatus == "Shipped" and @Published == "false" THEN   

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

        set @ItemID = Field(Row(@ItemIDRowSet,@i),"value")             
        set @url = Lookup("Product_Feed","BuyerURL","Variation",@ItemID)
        set @ImageURL = Lookup('Product_Feed','ImageURL','Variation',@ItemID)

]%%


HTML CODE HERE

%%[

    next @i
ENDIF                                           

]%%

Answer 1

If you only want to show sections with results, then you have to check the rowcount() to determine if a node exists in your XML -- just like you do in all of your other conditionals.

I'd also recommend setting variables for each rowset.

There's no sorting with AMPScript other than with LookupOrderedRows(), but that doesn't apply in your case.

You can utilize an XSLT Portfolio item to do the ordering. It'll definitely add a layer of complexity to your email, but there are all kinds of transformations you can do. SFMC supports XPath 1.0, not 2.0, so keep that in mind.

Here's a simple example of the AMPScript side:

%%[

  set @xml = AttributeValue("xml")
  Set @xslt = GetPortfolioItem("OrderConfirmation")
  Set @l = Length(@xslt)

  set @regionRowSet = BuildRowSetFromXML(@xml,"/OrderConfirmation/Shipping/Warehouse",0)

  if rowcount(@regionRowSet) > 0 then

   set @row = row(@regionRowSet,1)
   set @warehouse = field(@row,"Value")
   set @regionID = 0

   if not empty(@warehouse) then

    if indexOf(@warehouse,"Casper") > 0  then
      set @regionID = 1
    elseif indexOf(@warehouse,"Cody") > 0 then
      set @regionID = 2
    elseif indexOf(@warehouse,"Jackson") > 0 then
      set @regionID = 3
    endif

   endif

  endif

]%%%%=TreatAsContent(TransformXML(@xml, Substring(@xslt, 0, @l)))=%%

Reference

• TransformXML