XMLtable with Oracle 11g
Try this:
select X.COUNTRYNAME, Y.STATENAMEfrom XMLTEMP ,xmltable('/countries/country' passing MYDOC columns COUNTRYNAME varchar2(20) path './name', STATES xmltype path './states') X, xmltable('/states/state/name' passing X.STATES columns STATENAME varchar2(20) path '.') (+) YBecause you have multiple states you should join to another xml table. As some countries have no states then it needs to be a left outer join. I'm using the old method of (+) as I'm trying this on 10g and it seems there's a problem using left outer join in 10g but apparently it should be fine in 11g.