Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle: create view from XML (Read 875 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle: create view from XML
03.12.14 at 12:34:07
 
Oracle: create view from XML
 
To create a view from a XML document , the following technique can be used. Note that the XMLTable in the from list references the table xml_tab in the same from list.
 
with xml_tab as
(select xmltype(
   '<Projects>
     <Project>
        <Project>123</Project>
        <Description>blabla</Description>
     </Project>
     <Project>
        <Project>456</Project>
        <Description>blablabla</Description>
     </Project>
    </Projects>
     ' ) as response
 from dual
)
SELECT po.bbbb,po.description
    FROM xml_tab,
        XMLTable('/Projects/Project' PASSING xml_tab.response
                 COLUMNS
                   bbbb        varchar2(100) path 'Project'
                  ,description varchar2(100) path 'Description'
                  ) po
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1