XML Oracle - Extract Repeating Nodes

Question

I have xml doc loaded in to xmltype column in Oracle 11g. My doc has repeating nodes that I need to flatten. I have been using XMLTable but get the singleton error. I found a solution if child nodes were involved, but cannot get it to work with repeating nodes. Appreciate any help!

Here is example snippet of doc. The customer has two records in our xml doc:

<Customers>
  <Customer>
    <Id>123</Id>
    <Name>Acme</Name>
    <State>CA</State>
    <State>NY</State>
  </Customer>
  <Customer>
    <Id>456</Id>
    <Name>Acme</Name>
    <State>FL</State>
  </Customer>
</Customers>

For Acme customer 123, I am trying to get the following result set:

ID    Name    State

1. 123 Acme CA
2. 123 Acme NY

However, my query below returns

ID Name State

1. 123 Acme Ca
2. 123 Acme NY
3. 123 Acme FL

FL belongs to Acme's ID = 456 record. Here is my query, not sure how to force the join to restrict properly on id between my two XMLTables.

select distinct 
     xmlTable1.Id
    ,xmlTable1.Name
    ,xmlTable2.State
  from FILE_XML FX,
                      xmltable('//Customer'
                      PASSING FX.XML_COLUMN
                      columns
                      Name     VARCHAR2(255) PATH '//Name'      
                      ,Id      VARCHAR2(255) PATH '//Id'     
                      ,States  XmlType       Path '//Customer'
                      ) xmlTable1

  left join xmltable('//State'
                      passing xmlTable1.States
                      columns
                      State VARCHAR2(255)  path '.') XMLTable2
    on (1=1)
where xmltable1.id = 123;