0
ROW 1: 

 <a:employees xmlns:a="abc.com/123" xmlns:b="xyz.net/456">
<a:emp>
<a:name>Scott</a:name>
<b:favorites>
<b:color>red</b:color>
<b:color>orange</b:color>
</b:favorites>
</a:emp>
<a:emp>
<a:name>John</a:name>
<b:favorites>
<b:color>blue</b:color>
<b:color>green</b:color>
</b:favorites>
</a:emp>
</a:employees>


   Row2:

<a:employees xmlns:a="abc.com/123" xmlns:b="xyz.net/456" xmlns:c="pqr.edu/789>  
<a:emp>
<a:name>Tiger</a:name>
<b:favorites>
<c:phone>apple</c:phone>
<c:phone>samsung</c:phone>
<b:color>purple</b:color>
<b:color>pink</b:color>
</b:favorites>
</a:emp>
<a:emp>
<a:name>peter</a:name>
<b:favorites>
<c:phone>nokia</c:phone>
<b:color>violet</b:color>
<b:color>indigo</b:color>
</b:favorites>
</a:emp>
</a:employees>

Above two xml documents are the rows of an xml column xml_col of table your_table. I tried to parse the xml columns into relational data using the below query.

 select x.*
 from your_table y,
 XMLTable(XMLNamespaces('abc.com/123' as "a",
                   'xyz.net/456  as "b",'pqr.edu/789 as "c"),'*:employees'
 Passing y.xml_col
 Columns
 name varchar2(20) PATH '//*name',
 phone varchar2(20) PATH '//*phone',
 color varchar2(20) Path '//*:color')x

I am getting the below error message "XMLTABLE in oracle - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"

The problem is due to having multiple color and phone occurences. I want the multiple occurences to be in multiple rows. I dont mind even if the name value repeats. Output should be similar to the below format.

   Name     Phone       Color
   Scott                red
   Scott                orange
   John                 blue
   John                 green
   Tiger    Apple       purple
   Tiger    samsung     pink
   peter    nokia       violet
   peter                indigo

Thanks in advance

shiva
  • 11
  • 3
  • http://stackoverflow.com/questions/23412515/xmltable-in-oracle-xquery-dynamic-type-mismatch-expected-singleton-sequence – har07 Sep 11 '14 at 03:14
  • I have checked the above post, but that query does not contain namespaces. The structure of the xml is also different. I am looking for a query which contains namespaces. Thanks for the response – shiva Sep 11 '14 at 03:22

0 Answers0