This discussion is archived
3 Replies Latest reply: Sep 5, 2012 11:40 AM by AlexAnd RSS

load a file

627601 Newbie
Currently Being Moderated
Hi,

I know there are a few examples out there but I can't get the to work here, I am trying to create a table from the following xml file:

I have created a directory object on the database and can get the file into a clob:

SELECT xmltype(bfilename('SAMPLE_XML','SAMP_OFFICES_D.xml'), nls_charset_id('WE8ISO8859P1'))
FROM DUAL;

but I cant work out how to split in to cloumns so I can create a db lable and load the file in

<Table Name="SAMP_OFFICES_D">
     <SAMP_OFFICES_D>
          <OFFICE_KEY>1</OFFICE_KEY>
          <OFFICE_DSC>Montgomery Office</OFFICE_DSC>
          <COMPANY>Stockplus Inc.</COMPANY>
          <ORGANIZATION>Inbound Org.</ORGANIZATION>
          <DEPARTMENT>Entertainment Dept.</DEPARTMENT>
          <SEQUENCE>1</SEQUENCE>
          <COMPANY_KEY>10001</COMPANY_KEY>
          <ORG_KEY>1001</ORG_KEY>
          <DEPT_KEY>101</DEPT_KEY>
     </SAMP_OFFICES_D>
     <SAMP_OFFICES_D>
          <OFFICE_KEY>2</OFFICE_KEY>
          <OFFICE_DSC>Blue Bell Office</OFFICE_DSC>
          <COMPANY>Stockplus Inc.</COMPANY>
          <ORGANIZATION>Inbound Org.</ORGANIZATION>
          <DEPARTMENT>Entertainment Dept.</DEPARTMENT>
          <SEQUENCE>2</SEQUENCE>
          <COMPANY_KEY>10001</COMPANY_KEY>
          <ORG_KEY>1001</ORG_KEY>
          <DEPT_KEY>101</DEPT_KEY>
     </SAMP_OFFICES_D>
</Table>
  • 2. Re: load a file
    627601 Newbie
    Currently Being Moderated
    yes I have found a lot of these and tried to replicate the functionality but, I can't get it to work with this specific file as I am not sure how to factor this bit in:

    <Table Name="SAMP_OFFICES_D">
    <SAMP_OFFICES_D>

    in the examples they may have a simple:
    <INVOICES>
    <INVOICE>
    which would be represented in the code as :
    XMLTable(
    '/INVOICES/INVOICE'
    PASSING XMLType

    but I am not sure how to do the same thing with the <Table Name="SAMP_OFFICES_D"> bit which is why I raised the question.

    Edited by: Chumpski K on 05-Sep-2012 11:22
  • 3. Re: load a file
    AlexAnd Guru
    Currently Being Moderated
    SQL> select x.table_name, y.office_key, y.office_dsc
      2          from
      3               xmltable('*'
      4                         passing (xmltype(bfilename('MYDIR','your.xml'),nls_charset_id('AL32UTF8')))
      5                         columns table_name varchar2(20) path '/Table/@Name'
      6                                , other xmltype path '/Table/*'
      7                        ) x,
      8               xmltable('SAMP_OFFICES_D'
      9                         passing x.other
     10                         columns office_key varchar2(20) path 'OFFICE_KEY'
     11                                , office_dsc varchar2(20) path 'OFFICE_DSC'
     12                        )  y
     13  /
     
    TABLE_NAME           OFFICE_KEY           OFFICE_DSC
    -------------------- -------------------- --------------------
    SAMP_OFFICES_D       1                    Montgomery Office
    SAMP_OFFICES_D       2                    Blue Bell Office
     
    SQL> 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points