3 Replies Latest reply: Sep 5, 2012 1:40 PM by AlexAnd RSS

    load a file

    627601
      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
          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
            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>