Row Count error

Question

I wrote a code that reads that xml data and extracts all the values, separate the First Name from the Last Name and updates the DE, but I am still getting an error:

"Invalid index (parameter 2) passed to Row function. Index must be less than or equal to the row count.Index Value: 1 Row Count: 0 Function: Row(@customers,1)"

Can you please tell me what did I do wrong - I am struggling with this since yesterday.

    %%[
    var @xml
    set @xml = ""
    set @xml = concat(@xml, '<Customers>')
    set @xml = concat(@xml, '   <Customer_CustomerID="1235">')
    set @xml = concat(@xml, '      <CompanyName>AMO Food Market</CompanyName>')
    set @xml = concat(@xml, '      <Industry>Retail</Industry>')
    set @xml = concat(@xml, '      <ContactName>Anne Mollin</ContactName>')
    set @xml = concat(@xml, '      <ContactTitle>Marketing Manager</ContactTitle>')
    set @xml = concat(@xml, '      <Email>[email protected]</Email>')
    set @xml = concat(@xml, '      <FullAddress>')
    set @xml = concat(@xml, '        <Address>2732 Daver Str.</Address>')
    set @xml = concat(@xml, '        <City>Bandie</City>')
    set @xml = concat(@xml, '        <Region>OR</Region>')
    set @xml = concat(@xml, '        <PostalCode>94215</PostalCode>')
    set @xml = concat(@xml, '      </FullAddress>')
    set @xml = concat(@xml, '    </Customer>')
    set @xml = concat(@xml, '   <Customer CustomerID="1986">')
    set @xml = concat(@xml, '      <CompanyName>Folimi Tires</CompanyName>')
    set @xml = concat(@xml, '      <Industry>Cars</Industry>')
    set @xml = concat(@xml, '      <ContactName>Adrian Moods</ContactName>')
    set @xml = concat(@xml, '      <ContactTitle>Sales Representative</ContactTitle>')
    set @xml = concat(@xml, '      <Email>[email protected]</Email>')
    set @xml = concat(@xml, '      <FullAddress>')
    set @xml = concat(@xml, '        <Address>City Mall 100 Main St.</Address>')
    set @xml = concat(@xml, '        <City>Dondie</City>')
    set @xml = concat(@xml, '        <Region>OR</Region>')
    set @xml = concat(@xml, '        <PostalCode>93210</PostalCode>')
    set @xml = concat(@xml, '      </FullAddress>')
    set @xml = concat(@xml, '    </Customer>')
    set @xml = concat(@xml, '</Customers>')

    set @customers = BuildRowsetFromXml(@xml, "Customers", 1)
    set @CustomerID = Field(Row(@customers,1),"CustomerID_att")

    set @companyNames = BuildRowsetFromXml(@xml, "Customer/CompanyName", 1)
    set @companyName = Field(Row(@companyNames,1),1)

    set @industries = BuildRowsetFromXml(@xml, "Customer/Industry", 1)
    set @industry = Field(Row(@industries,1),1)

    set @contactNames = BuildRowsetFromXml(@xml, "Customer/ContactName", 1)
    set @full_name = Field(Row(@contactNames,1),1)

    set @emails = BuildRowsetFromXml(@xml, "Customer/Email", 1)
    set @email = Field(Row(@emails,1),1)

    set @addresses = BuildRowsetFromXml(@xml, "Customer/FullAddress/Address", 1)
    set @address = Field(Row(@addresses,1),1)

    set @cities = BuildRowsetFromXml(@xml, "Customer/FullAddress/City", 1)
    set @city = Field(Row(@cities,1),1)

    set @regions = BuildRowsetFromXml(@xml, "Customer/FullAddress/Region", 1)
    set @region = Field(Row(@regions,1),1)

    set @postalCodes = BuildRowsetFromXml(@xml, "Customer/FullAddress/PostalCode", 1)
    set @postalCode = Field(Row(@postalCodes,1),1)

    if indexOf(@full_name, " ") > 0 then
      set @first_name = substring(@full_name,1,subtract(indexOf(@full_name," "),1))
      set @last_name = substring(@full_name, add(indexOf(@full_name," "),1),length(@full_name))
    endif

     UpsertDE("AMPscript Functions 3 - v2",1,"xml",@xml,"email",@email,"firstname",@firstname,"lastname",@lastname)
     UpsertDE("AMPscript Functions 3 - v2",1,"xml",@xml,"companyname",@companyName,"industry",@industry,"address",@address,"city",@city)
     UpsertDE("AMPscript Functions 3 - v2",1,"xml",@xml,"region",@region,"postalcode",@postalCode, "customerID", @customerID)

]%%

Gortonington · Accepted Answer · 2019-03-22 17:14:23Z

There were a couple things wrong with your code:

Your first Customer row had an underscore instead of a space - Customer_CustomerID instead of Customer CustomerID
Your path for calling the XML via BuildRowsetFromXML was incorrect. you need to include the root inside of your path. BuildRowsetFromXml(@xml, "Customers", 1) must become BuildRowsetFromXml(@xml, "//Customer", 1). This is true for all of your functions

After these changes are made, your code works as expected. See below working version:

%%[

var @xml

set @xml = ""

set @xml = concat(@xml, '<Customers>') 

set @xml = concat(@xml, '   <Customer CustomerID="1235">') 

set @xml = concat(@xml, '      <CompanyName>AMO Food Market</CompanyName>')

set @xml = concat(@xml, '      <Industry>Retail</Industry>')

set @xml = concat(@xml, '      <ContactName>Anne Mollin</ContactName>') 

set @xml = concat(@xml, '      <ContactTitle>Marketing Manager</ContactTitle>') 

set @xml = concat(@xml, '      <Email>[email protected]</Email>') 

set @xml = concat(@xml, '      <FullAddress>') 

set @xml = concat(@xml, '        <Address>2732 Daver Str.</Address>') 

set @xml = concat(@xml, '        <City>Bandie</City>') 

set @xml = concat(@xml, '        <Region>OR</Region>') 

set @xml = concat(@xml, '        <PostalCode>94215</PostalCode>') 

set @xml = concat(@xml, '      </FullAddress>') 

set @xml = concat(@xml, '   </Customer>') 

set @xml = concat(@xml, '   <Customer CustomerID="1986">') 

set @xml = concat(@xml, '      <CompanyName>Folimi Tires</CompanyName>')

set @xml = concat(@xml, '      <Industry>Cars</Industry>') 

set @xml = concat(@xml, '      <ContactName>Adrian Moods</ContactName>') 

set @xml = concat(@xml, '      <ContactTitle>Sales Representative</ContactTitle>') 

set @xml = concat(@xml, '      <Email>[email protected]</Email>') 

set @xml = concat(@xml, '      <FullAddress>') 

set @xml = concat(@xml, '        <Address>City Mall 100 Main St.</Address>') 

set @xml = concat(@xml, '        <City>Dondie</City>') 

set @xml = concat(@xml, '        <Region>OR</Region>') 

set @xml = concat(@xml, '        <PostalCode>93210</PostalCode>') 

set @xml = concat(@xml, '      </FullAddress>') 

set @xml = concat(@xml, '   </Customer>')

set @xml = concat(@xml, '</Customers>') 


set @customers = BuildRowsetFromXml(@xml, "//Customer", 1)

set @CustomerID = Field(Row(@customers,1),"CustomerID_att")



set @companyNames = BuildRowsetFromXml(@xml, "//Customer/CompanyName", 1)

set @companyName = Field(Row(@companyNames,1),1)



set @industries = BuildRowsetFromXml(@xml, "//Customer/Industry", 1)

set @industry = Field(Row(@industries,1),1)



set @contactNames = BuildRowsetFromXml(@xml, "//Customer/ContactName", 1)

set @full_name = Field(Row(@contactNames,1),1)



set @emails = BuildRowsetFromXml(@xml, "//Customer/Email", 1)

set @email = Field(Row(@emails,1),1)



set @addresses = BuildRowsetFromXml(@xml, "//Customer/FullAddress/Address", 1)

set @address = Field(Row(@addresses,1),1)



set @cities = BuildRowsetFromXml(@xml, "//Customer/FullAddress/City", 1)

set @city = Field(Row(@cities,1),1)



set @regions = BuildRowsetFromXml(@xml, "//Customer/FullAddress/Region", 1)

set @region = Field(Row(@regions,1),1)



set @postalCodes = BuildRowsetFromXml(@xml, "//Customer/FullAddress/PostalCode", 1)

set @postalCode = Field(Row(@postalCodes,1),1)



if indexOf(@full_name, " ") > 0 then

  set @first_name = substring(@full_name,1,subtract(indexOf(@full_name," "),1))

  set @last_name = substring(@full_name, add(indexOf(@full_name," "),1),length(@full_name))

endif




]%%

EDIT:

Added loop in:

%%[

var @xml

set @xml = ""

set @xml = concat(@xml, '<Customers>') 

set @xml = concat(@xml, '   <Customer CustomerID="1235">') 

set @xml = concat(@xml, '      <CompanyName>AMO Food Market</CompanyName>')

set @xml = concat(@xml, '      <Industry>Retail</Industry>')

set @xml = concat(@xml, '      <ContactName>Anne Mollin</ContactName>') 

set @xml = concat(@xml, '      <ContactTitle>Marketing Manager</ContactTitle>') 

set @xml = concat(@xml, '      <Email>[email protected]</Email>') 

set @xml = concat(@xml, '      <FullAddress>') 

set @xml = concat(@xml, '        <Address>2732 Daver Str.</Address>') 

set @xml = concat(@xml, '        <City>Bandie</City>') 

set @xml = concat(@xml, '        <Region>OR</Region>') 

set @xml = concat(@xml, '        <PostalCode>94215</PostalCode>') 

set @xml = concat(@xml, '      </FullAddress>') 

set @xml = concat(@xml, '   </Customer>') 

set @xml = concat(@xml, '   <Customer CustomerID="1986">') 

set @xml = concat(@xml, '      <CompanyName>Folimi Tires</CompanyName>')

set @xml = concat(@xml, '      <Industry>Cars</Industry>') 

set @xml = concat(@xml, '      <ContactName>Adrian Moods</ContactName>') 

set @xml = concat(@xml, '      <ContactTitle>Sales Representative</ContactTitle>') 

set @xml = concat(@xml, '      <Email>[email protected]</Email>') 

set @xml = concat(@xml, '      <FullAddress>') 

set @xml = concat(@xml, '        <Address>City Mall 100 Main St.</Address>') 

set @xml = concat(@xml, '        <City>Dondie</City>') 

set @xml = concat(@xml, '        <Region>OR</Region>') 

set @xml = concat(@xml, '        <PostalCode>93210</PostalCode>') 

set @xml = concat(@xml, '      </FullAddress>') 

set @xml = concat(@xml, '   </Customer>')

set @xml = concat(@xml, '</Customers>') 


set @customers = BuildRowsetFromXml(@xml, "//Customer", 1)

FOR @i = 1 TO Rowcount(@customers) DO

    set @CustomerID = Field(Row(@customers,@i),"CustomerID_att")



    set @companyNames = BuildRowsetFromXml(@xml, "//Customer/CompanyName", 1)

    set @companyName = Field(Row(@companyNames,@i),1)



    set @industries = BuildRowsetFromXml(@xml, "//Customer/Industry", 1)

    set @industry = Field(Row(@industries,@i),1)



    set @contactNames = BuildRowsetFromXml(@xml, "//Customer/ContactName", 1)

    set @full_name = Field(Row(@contactNames,@i),1)



    set @emails = BuildRowsetFromXml(@xml, "//Customer/Email", 1)

    set @email = Field(Row(@emails,@i),1)



    set @addresses = BuildRowsetFromXml(@xml, "//Customer/FullAddress/Address", 1)

    set @address = Field(Row(@addresses,@i),1)



    set @cities = BuildRowsetFromXml(@xml, "//Customer/FullAddress/City", 1)

    set @city = Field(Row(@cities,@i),1)



    set @regions = BuildRowsetFromXml(@xml, "//Customer/FullAddress/Region", 1)

    set @region = Field(Row(@regions,@i),1)



    set @postalCodes = BuildRowsetFromXml(@xml, "//Customer/FullAddress/PostalCode", 1)

    set @postalCode = Field(Row(@postalCodes,@i),1)



    if indexOf(@full_name, " ") > 0 then

      set @first_name = substring(@full_name,1,subtract(indexOf(@full_name," "),1))

      set @last_name = substring(@full_name, add(indexOf(@full_name," "),1),length(@full_name))

    endif

]%%

<hr>
Row %%=v(@i)=%%:<br /><br />
%%=v(@CustomerID)=%%<br>
%%=v(@companyName)=%%<br>
%%=v(@industry)=%%<br>
%%=v(@full_name)=%%<br>
%%=v(@email)=%%<br>
%%=v(@address)=%%<br>
%%=v(@city)=%%<br>
%%=v(@region)=%%<br>
%%=v(@postalCode)=%%<br>
%%=v(@first_name)=%%<br>
%%=v(@last_name)=%%<br>
<br><br>

%%[
NEXT @i
]%%

Outputs:

Row 1:

1235
AMO Food Market
Retail
Anne Mollin
[email protected]
2732 Daver Str.
Bandie
OR
94215
Anne
Mollin


Row 2:

1986
Folimi Tires
Cars
Adrian Moods
[email protected]
City Mall 100 Main St.
Dondie
OR
93210
Adrian
Moods

Thank you !!!!!!! ... the only thing that I need to do is do add a loop - I have multiple customers with different ID's. — Calin Bostan, Mar 22, 2019 at 16:38
I've added the loop but it doesn't do it, it only takes the first "CustomerID" that it finds. — Calin Bostan, Mar 22, 2019 at 17:02
Did you change Field(Row(@emails,1),1) to Field(Row(@emails,@i),1) with @i being the count var for your for loop? — Gortonington, Mar 22, 2019 at 17:06
@CalinBostan I added a version with the loop into my answer. — Gortonington, Mar 22, 2019 at 17:15

Adam Spriggs · Accepted Answer · 2019-03-22 17:50:38Z

I'd also recommend checking the rowcount before you retrieve each element value. For example.

%%[

var @xml, @isXML, @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

]%%

Source

Stack Exchange Network

Row Count error

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
marketing-cloud
ampscript
xml
.

Hot Network Questions

Row Count error

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged marketing-cloudampscriptxml.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
marketing-cloud
ampscript
xml
.