10 Replies Latest reply: Apr 15, 2011 2:54 AM by 852132 RSS

    Load an XML file into table(s)

    852132
      Hi ,

      I have to load data from an xml file into an Oracle DB but I never used this king of process before. The purpose is to use as much as possible Oracle standard features ( stored procedures , functions , API's ).
      Can someone explain me in simple explanations how to do it ? Thanks in advance for your help.

      The XML must not be stored in the database , only the final tables
      Values can be inserted , updated , or deleted from the final tables

      Here are the versions of the tools I am using :
      Oracle RDBMS : 10.2.0.4.0
      Oracle Applications : 11.5.10.2
      Toad : 9.5.0.31
      SQL Plus : 8.0.6.0.0

      The header of the xsd :

      <?xml version="1.0" encoding="windows-1252" ?>
      - <!-- edited with XMLSPY v2004 rel. 4 U (http://www.xmlspy.com) by erik de bruyn (Graydon)
      -->
      - <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://www.w3schools.com" targetNamespace="http://www.w3schools.com" elementFormDefault="qualified">

      An extract of the xml :

      <?xml version="1.0" encoding="windows-1252" ?>
      - <GraydonBeDialogue>
      <TransactionCode>RTB</TransactionCode>
      - <Table ClassTable="Country">
      - <TableEntry>
      <TableLanguage>N</TableLanguage>
      <TableCode>AD</TableCode>
      <TableValue>Andorra</TableValue>
      </TableEntry>
      - <TableEntry>
      <TableLanguage>N</TableLanguage>
      <TableCode>AE</TableCode>
      <TableValue>Verenigde Arabische Emiraten</TableValue>
      </TableEntry>
      </Table>
      - <Table ClassTable="Summons">
      - <TableEntry>
      <TableLanguage>N</TableLanguage>
      <TableCode>D</TableCode>
      <TableValue>De dagvaarding is het gevolg</TableValue>
      </TableEntry>
      - <TableEntry>
      <TableLanguage>N</TableLanguage>
      <TableCode>S</TableCode>
      <TableValue>Doorgehaald bij de arbeidsrechtbank</TableValue>
      </TableEntry>
      </Table>
      </GraydonBeDialogue>

      The result I would have :

      Two tables ( Country and Summons ) , each containing 3 columns ( TableLanguage , TableCode , TableValue ) :

      Table Country : TableLanguage TableCode TableValue
      ------------------------------------------------------
      N AD Andorra
      N AE Verenigde Arabische Emiraten

      Table Summons : TableLanguage TableCode TableValue
      ------------------------------------------------------
      N D De dagvaarding is het gevolg
      N S Doorgehaald bij de arbeidsrechtbank
        • 1. Re: Load an XML file into table(s)
          AlexAnd
          for example
          create table TABLE_NAME as
          
          select extract(x,'/root/node/text()').getStringVal() x
          from(
              select
              xmltype (
                  '<?xml version="1.0" encoding="ISO-8859-1" ?>
                      <root>
                        <node>data</node>
                        <node2>other data</node2>
                      </root>') as x        
              from dual
          )
          • 2. Re: Load an XML file into table(s)
            848583
            Hi ,

            Please view the below links . It helps you as these are also of the same discussion.


            PL/SQL XML parsing into database
            http://kr.forums.oracle.com/forums/thread.jspa?threadID=1113266

            populate xml data into tables
            populate xml data into tables


            XML File into Oracle table
            XML File into Oracle table

            Load XML records in a normal table
            http://kr.forums.oracle.com/forums/thread.jspa?threadID=2188443


            Thanks,

            Balaji K.
            • 3. Re: Load an XML file into table(s)
              AlexAnd
              for table Country
              create table Country as
              with t as (
                  select
                  xmltype (
                      '<?xml version="1.0" encoding="windows-1252" ?>
              <GraydonBeDialogue>
                   <TransactionCode>RTB</TransactionCode>
                        <Table ClassTable="Country">
                             <TableEntry>
                                  <TableLanguage>N</TableLanguage>
                                  <TableCode>AD</TableCode>
                                  <TableValue>Andorra</TableValue>
                             </TableEntry>
                             <TableEntry>
                                  <TableLanguage>N</TableLanguage>
                                  <TableCode>AE</TableCode>
                                  <TableValue>Verenigde Arabische Emiraten</TableValue>
                             </TableEntry>
                        </Table>
                        <Table ClassTable="Summons">
                             <TableEntry>
                                  <TableLanguage>N</TableLanguage>
                                  <TableCode>D</TableCode>
                                  <TableValue>De dagvaarding is het gevolg</TableValue>
                             </TableEntry>
                             <TableEntry>
                                  <TableLanguage>N</TableLanguage>
                                  <TableCode>S</TableCode>
                                  <TableValue>Doorgehaald bij de arbeidsrechtbank</TableValue>
                             </TableEntry>
                        </Table>
              </GraydonBeDialogue>') as xml        
                  from dual
              )
              select x.TableLanguage, x.TableCode, x.TableValue
              from t
              ,xmltable('/GraydonBeDialogue/Table[@ClassTable="Country"]/TableEntry'
                                 passing t.xml
                                 columns TableLanguage varchar2(50) path '/TableEntry/TableLanguage'
                                 , TableCode varchar2(50) path '/TableEntry/TableCode'
                                 , TableValue varchar2(50) path '/TableEntry/TableValue'                         
                                        ) x
              • 4. Re: Load an XML file into table(s)
                852132
                Thanks for your answer. I just modified a little but it works fine.

                Do you know if it is possible to automatically create the tables "Country" and "Summons" ?
                The main idea is to try to find a generic way to automically create the tables , watever the content of the xml , so completely different xml files can be loaded in the system without having to manipulate the data to create the tables.

                Rgds,
                Patrick.
                • 5. Re: Load an XML file into table(s)
                  AlexAnd
                  >
                  The main idea is to try to find a generic way to automically create the tables , watever the content of the xml , so completely different xml files can be loaded in the system without having to manipulate the data to create the tables.
                  >

                  why "automically create the tables" ?
                  if i have 100 xml -> i have 100 table (not less)

                  and how you automically distinguish any attribute for different xml files?
                  you must know structure of xml for automation.

                  may be create one table and use it for work with xml files?

                  alex
                  • 6. Re: Load an XML file into table(s)
                    AlexAnd
                    declare
                     stmt varchar2(32000);
                    
                    begin
                    
                    for y in (select ClassTable from (
                                        select extract((XMLTYPE(bfilename('XMLDIR','t.xml'),NLS_CHARSET_ID('AL32UTF8'))),'*') as xml
                                            from dual) t
                                            ,xmltable('/GraydonBeDialogue/Table/@ClassTable'
                                               passing t.xml                        
                                                columns ClassTable varchar2(50) path '/'             
                                                      ) x    
                        ) 
                    loop
                            stmt := 'create table '|| y.ClassTable ||' as '||  
                                    ' select x.TableLanguage, x.TableCode, x.TableValue ' ||
                                            'from (select extract((XMLTYPE(bfilename(''XMLDIR'',''t.xml''),NLS_CHARSET_ID(''AL32UTF8''))),''*'') as xml '||
                                                    'from dual)t'||
                                      ' ,xmltable(''/GraydonBeDialogue/Table[@ClassTable="' ||  y.ClassTable || '"]/TableEntry'''||
                                       ' passing t.xml'||
                                       ' columns TableLanguage varchar2(50) path ''/TableEntry/TableLanguage'''||
                                       ' , TableCode varchar2(50) path ''/TableEntry/TableCode'''||
                                       ' , TableValue varchar2(50) path ''/TableEntry/TableValue'''||                         
                                             ' ) x';
                                                                        
                                 dbms_output.put_line(stmt);                                     
                    
                    end loop;
                    
                    end;
                    • 7. Re: Load an XML file into table(s)
                      852132
                      OK.

                      From my point of view , it was possible to automatically create the tables when the xsd is registered in the database. Apparently I was wrong.

                      Thanks.
                      Rgds,
                      Patrick.
                      • 8. Re: Load an XML file into table(s)
                        BluShadow
                        user1429570 wrote:
                        OK.

                        From my point of view , it was possible to automatically create the tables when the xsd is registered in the database. Apparently I was wrong.

                        Thanks.
                        Rgds,
                        Patrick.
                        You mean like mdrake demonstrates on this thread over in the XML DB forum...

                        Re: XML file processing into oracle
                        • 9. Re: Load an XML file into table(s)
                          852132
                          It worked fine for the first xml. But I have problems with the next one :

                          For example , I would like to retrieve the format date.

                          I use this select but it does not work and I don't know why. Could you help ?

                          SELECT DateFormat
                          FROM XMLTable('/GraydonBeInformation'
                          passing xmltype(bfilename('GRAYDONXMLDIR','ReviewExamples1.XML'), nls_charset_id('WE8MSWIN1252'))
                          columns DateFormat varchar2(100) path 'Default/text()'
                          );

                          Here is an extract of the xml :

                          <?xml version="1.0" encoding="windows-1252" ?>
                          - <GraydonBeDialogue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.w3schools.com Z:\PROGRAMMATIE\AS400\projecten\XML\GraydonBe\InfoReview\Review.xsd" xmlns="http://www.w3schools.com">
                          <Transaction>PDT</Transaction>
                          - <GraydonBeInformation>
                          - <Default>
                          <DateFormat>YYYYMMDD</DateFormat>
                          <TimeFormat>HHMMSS</TimeFormat>
                          <Currency>EUR</Currency>
                          <CountryCode>BE</CountryCode>
                          <DecimalSign>.</DecimalSign>
                          </Default>
                          - <Header>
                          <Name>GRAYDON BELGIUM NV /SA</Name>
                          <ContentGenerationDate>20071004</ContentGenerationDate>
                          <ContentGenerationTime>182121</ContentGenerationTime>
                          <Product>REV</Product>
                          <ClientNumber>243</ClientNumber>
                          </Header>
                          - <Body>
                          - <Msg2>
                          - <MsgHeader Type="FlashBack">
                          <MsgDate>20071004</MsgDate>
                          <MsgCode Table="RevDsoMsg">2</MsgCode>
                          • 10. Re: Load an XML file into table(s)
                            852132
                            It worked fine for the first xml. But I have problems with the next one :

                            For example , I would like to retrieve the format date.

                            I use this select but it does not work and I don't know why. Could you help ?

                            SELECT DateFormat
                            FROM XMLTable('/GraydonBeInformation'
                            passing xmltype(bfilename('GRAYDONXMLDIR','ReviewExamples1.XML'), nls_charset_id('WE8MSWIN1252'))
                            columns DateFormat varchar2(100) path 'Default/text()'
                            );

                            Here is an extract of the xml :

                            <?xml version="1.0" encoding="windows-1252" ?>
                            - <GraydonBeDialogue xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.w3schools.com Z:\PROGRAMMATIE\AS400\projecten\XML\GraydonBe\InfoReview\Review.xsd" xmlns="http://www.w3schools.com">
                            <Transaction>PDT</Transaction>
                            - <GraydonBeInformation>
                            - <Default>
                            <DateFormat>YYYYMMDD</DateFormat>
                            <TimeFormat>HHMMSS</TimeFormat>
                            <Currency>EUR</Currency>
                            <CountryCode>BE</CountryCode>
                            <DecimalSign>.</DecimalSign>
                            </Default>
                            - <Header>
                            <Name>GRAYDON BELGIUM NV /SA</Name>
                            <ContentGenerationDate>20071004</ContentGenerationDate>
                            <ContentGenerationTime>182121</ContentGenerationTime>
                            <Product>REV</Product>
                            <ClientNumber>243</ClientNumber>
                            </Header>
                            - <Body>
                            - <Msg2>
                            - <MsgHeader Type="FlashBack">
                            <MsgDate>20071004</MsgDate>
                            <MsgCode Table="RevDsoMsg">2</MsgCode>