5 Replies Latest reply: Feb 10, 2014 11:49 PM by taohiko RSS

    Duplicate child node in XML content

    taohiko

      Hi All

       

      I got below example XML data from developer and they need to load data to table in Oracle database (10g).

       

      <Table>

        <Rows>

        <Row>

         <CellCount>3</CellCount>

        <Cells>

         <Cell>John Tom</Cell>

         <Cell>jt@xxx.com</Cell>

         <Cell>2013-08-30 12:20:37.0</Cell>

         </Cells>

         </Row>

         <Row>

        <CellCount>3</CellCount>

        <Cells>

         <Cell>Micky Mouse</Cell>

         <Cell>mm@xxx.com</Cell>

         <Cell>2013-08-30 12:20:37.0</Cell>

        </Cells>

        </Row> 

         </Rows>

        </Table>

       

      But I got problem because child node that is duplicated name and developer use some tool to generate that and cannot modify tag before send to me.

       

      How can I do for this case?

       

      Thanks in advance.

      Hiko

        • 1. Re: Duplicate child node in XML content
          odie_63

          Hi,

           

          Could you give a more precise database version please?

          SELECT * FROM v$version;

           

          What's the structure of the target table?

          • 2. Re: Duplicate child node in XML content
            taohiko

            Hi Odie_63,

             

            ORACLE version 10.2.0.1 and below is structure of table

            table EMP

            ( name varchar2(50),

               email varchar2(50),

              sign_date date)

             

            Thanks,

            Hiko

            • 3. Re: Duplicate child node in XML content
              Jason_(A_Non)
              SELECT name, email, sign_date
              FROM XMLTable('/Table/Rows/Row/Cells'
                -- hard-coded XML as unsure where your data comes from
                            PASSING XMLTYPE('<Table>
                <Rows>
                <Row>
                 <CellCount>3</CellCount>
                <Cells>
                 <Cell>John Tom</Cell>
                 <Cell>jt@xxx.com</Cell>
                 <Cell>2013-08-30 12:20:37.0</Cell>
                 </Cells>
                 </Row>
                 <Row>
                <CellCount>3</CellCount>
                <Cells>
                 <Cell>Micky Mouse</Cell>
                 <Cell>mm@xxx.com</Cell>
                 <Cell>2013-08-30 12:20:37.0</Cell>
                </Cells>
                </Row> 
                 </Rows>
                </Table>')
                            COLUMNS
                            name      VARCHAR2(30) PATH 'Cell[1]',
                            email     VARCHAR2(30) PATH 'Cell[2]',
                            sign_date VARCHAR2(22) PATH 'Cell[3]');
              -- sign_date is neither a date nor a timestamp for Oracle default conversion so will need to to_date it yourself
              

              That should get you started.  XMLTable was part of 10.2.0.1.  Is there any chance to upgrade to at least 10.2.0.5 just to pick up bug fixes/new features?

              • 4. Re: Duplicate child node in XML content
                odie_63

                Here's an example, assuming the file resides in a directory.

                 

                SQL> create table emp (

                  2    name      varchar2(50)

                  3  , email     varchar2(50)

                  4  , sign_date date

                  5  ) ;

                 

                Table created

                 

                SQL>

                SQL> insert into emp (name, email, sign_date)

                  2  select extractvalue(x.column_value, '/Cells/Cell[1]')

                  3       , extractvalue(x.column_value, '/Cells/Cell[2]')

                  4       , to_timestamp(extractvalue(x.column_value, '/Cells/Cell[3]'), 'YYYY-MM-DD HH24:MI:SS.FF')

                  5  from table(

                  6         xmlsequence(

                  7           extract(

                  8             xmltype(

                  9               bfilename('XML_DIR', 'table.xml')

                10             , nls_charset_id('AL32UTF8')  -- indicates the real encoding of the file

                11             )

                12           , '/Table/Rows/Row/Cells'

                13           )

                14         )

                15       ) x

                16  ;

                 

                2 rows inserted

                 

                SQL> select * from emp;

                 

                NAME                 EMAIL                SIGN_DATE

                -------------------- -------------------- -------------------

                John Tom             jt@xxx.com           30/08/2013 12:20:37

                Micky Mouse          mm@xxx.com           30/08/2013 12:20:37

                 

                Although you could use XMLTable (easier to write) in your version, I'd still rely on XMLSequence (faster).

                • 5. Re: Duplicate child node in XML content
                  taohiko

                  Hi Odie and Jason

                   

                  That is excellent.

                   

                  Thank you very much for your example.

                  Hiko