Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Apr 28th, 2012  |  syntax: None  |  size: 1.60 KB  |  hits: 142  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. XMLtable with Oracle 11g
  2. create table xmltemp (mydoc xmltype)
  3.        
  4. insert into xmltemp values (
  5. xmltype
  6. ('<?xml version="1.0"?>
  7. <countries>
  8.   <country>
  9.     <name>Canada</name>
  10.   </country>
  11.   <country>
  12.     <name>US</name>
  13.     <states>
  14.       <state>
  15.         <name>Washington</name>
  16.         <name>Oregon</name>        
  17.       </state>
  18.     </states>
  19.   </country>
  20. </countries>
  21. ')
  22. )
  23.        
  24. Canada,
  25. US,Washington
  26. US,Oregon
  27.        
  28. select
  29. countryname
  30. from xmltemp,
  31. xmltable('/countries/country' passing mydoc
  32.    columns countryname varchar2(10) path 'name')
  33.        
  34. select
  35. statename
  36. from xmltemp,
  37. xmltable('/countries/country/states/state/name' passing mydoc
  38.    columns statename   varchar2(20) path '.') c
  39.        
  40. select
  41. statename
  42. from xmltemp,
  43. xmltable('/countries/country/states/state/name' passing mydoc
  44.    columns statename   varchar2(20) path '.',
  45.            countryname varchar2(20) path '../../../name') c
  46.        
  47. ORA-19110: unsupported XQuery expression
  48.        
  49. select
  50. countryname,
  51. statename
  52. from xmltemp,
  53. xmltable('/countries/country' passing mydoc
  54.    columns countryname varchar2(10) path 'name',
  55.            statename   varchar2(20) path 'states/state/name') c
  56.        
  57. ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton
  58. sequence - got multi-item sequence
  59.        
  60. Canada,
  61. US,Washington
  62. US,Oregon
  63.        
  64. select      X.COUNTRYNAME, Y.STATENAME
  65. from        XMLTEMP
  66.            ,xmltable('/countries/country'
  67.                      passing MYDOC
  68.                      columns COUNTRYNAME varchar2(20) path './name', STATES xmltype path './states') X
  69.            ,xmltable('/states/state/name' passing X.STATES columns STATENAME varchar2(20) path '.') (+) Y
create a new version of this paste RAW Paste Data