1 2 Previous Next 16 Replies Latest reply: Dec 7, 2012 7:20 AM by BluShadow Go to original post RSS
      • 15. Re: Converting XML file into .txt file or .csv format
        Rahul_India
        BluShadow wrote:

        And your database version is?
        Look up the thread.Its 11gR2 :)
        • 16. Re: Converting XML file into .txt file or .csv format
          BluShadow
          Rahul_India wrote:
          BluShadow wrote:

          And your database version is?
          Look up the thread.Its 11gR2 :)
          Ah, yes, hidden at the bottom of all the unformatted XML.

          Well, you're going to have to copy and paste exactly what you've done, because it's working when I run it...
          SQL> select * from v$version;
          
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          PL/SQL Release 11.2.0.3.0 - Production
          CORE    11.2.0.3.0      Production
          TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
          NLSRTL Version 11.2.0.3.0 - Production
          
          SQL> ed
          Wrote file afiedt.buf
          
            1  WITH t as (select XMLTYPE('
            2  <RECSET xmlns:aa="http://www.w3.org">
            3    <aa:REC>
            4      <aa:COUNTRY>1</aa:COUNTRY>
            5      <aa:POINT>1800</aa:POINT>
            6      <aa:USER_INFO>
            7        <aa:USER_ID>1</aa:USER_ID>
            8        <aa:TARGET>28</aa:TARGET>
            9        <aa:STATE>6</aa:STATE>
           10        <aa:TASK>12</aa:TASK>
           11      </aa:USER_INFO>
           12      <aa:USER_INFO>
           13        <aa:USER_ID>5</aa:USER_ID>
           14        <aa:TARGET>19</aa:TARGET>
           15        <aa:STATE>1</aa:STATE>
           16        <aa:TASK>90</aa:TASK>
           17      </aa:USER_INFO>
           18    </aa:REC>
           19    <aa:REC>
           20      <aa:COUNTRY>2</aa:COUNTRY>
           21      <aa:POINT>2400</aa:POINT>
           22      <aa:USER_INFO>
           23        <aa:USER_ID>3</aa:USER_ID>
           24        <aa:TARGET>14</aa:TARGET>
           25        <aa:STATE>7</aa:STATE>
           26        <aa:TASK>5</aa:TASK>
           27      </aa:USER_INFO>
           28    </aa:REC>
           29  </RECSET>') as xml from dual)
           30  -- END OF TEST DATA
           31  select x.country, x.point, y.user_id, y.target, y.state, y.task
           32  from t
           33      ,XMLTABLE(XMLNAMESPACES('http://www.w3.org' as "aa"),
           34                '/RECSET/aa:REC'
           35                PASSING t.xml
           36                COLUMNS country NUMBER PATH '/aa:REC/aa:COUNTRY'
           37                       ,point   NUMBER PATH '/aa:REC/aa:POINT'
           38                       ,user_info XMLTYPE PATH '/aa:REC/*'
           39               ) x
           40      ,XMLTABLE(XMLNAMESPACES('http://www.w3.org' as "aa"),
           41                '/aa:USER_INFO'
           42                PASSING x.user_info
           43                COLUMNS user_id NUMBER PATH '/aa:USER_INFO/aa:USER_ID'
           44                       ,target  NUMBER PATH '/aa:USER_INFO/aa:TARGET'
           45                       ,state   NUMBER PATH '/aa:USER_INFO/aa:STATE'
           46                       ,task    NUMBER PATH '/aa:USER_INFO/aa:TASK'
           47*              ) y
          SQL> /
          
             COUNTRY      POINT    USER_ID     TARGET      STATE       TASK
          ---------- ---------- ---------- ---------- ---------- ----------
                   1       1800          1         28          6         12
                   1       1800          5         19          1         90
                   2       2400          3         14          7          5
          1 2 Previous Next