4

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;
rbgilder
  • 41
  • 3
  • Strange, but it returns correct result set (what you would like it to return) when I run it. Had to change `customerid` in the `WHERE` clause to `id`, otherwise it wouldn't work. Is the XML you have provided as a sample data all there is in the `XML_COLUMN`? – Przemyslaw Kruglej Oct 25 '13 at 17:49
  • All of the data is in the XML_COLUMN. Do you get just two records in the result set (it excludes FL)? For me, I it always includes the third record with the state being FL but with incorrect id of 123. Also, I have corrected my where clause on the select. Thanks for pointing that out! – rbgilder Nov 01 '13 at 20:19
  • Found my answer here: https://forums.oracle.com/message/4256301 – rbgilder Nov 01 '13 at 23:03

0 Answers0