1

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>calin.bostan@osf-global.com</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>adrian.moods@folimitires.com</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)

]%%
2

There were a couple things wrong with your code:

  1. Your first Customer row had an underscore instead of a space - Customer_CustomerID instead of Customer CustomerID
  2. 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>calin.bostan@osf-global.com</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>adrian.moods@folimitires.com</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>calin.bostan@osf-global.com</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>adrian.moods@folimitires.com</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
calin.bostan@osf-global.com
2732 Daver Str.
Bandie
OR
94215
Anne
Mollin


Row 2:

1986
Folimi Tires
Cars
Adrian Moods
adrian.moods@folimitires.com
City Mall 100 Main St.
Dondie
OR
93210
Adrian
Moods
| improve this answer | |
  • 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 '19 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 '19 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 '19 at 17:06
  • @CalinBostan I added a version with the loop into my answer. – Gortonington Mar 22 '19 at 17:15
  • 1
    Thank you very much!!! It works now. – Calin Bostan Mar 22 '19 at 20:29
1

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

| improve this answer | |

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.