Extract xml data using oracle query

Question

Oracle version 11g

HI , When trying to read the XML in a LOOP the query is giving an error but if, I change this to a non existing path then the query is running fine . However the @Name is not fetching is the expected . What should I correct here to make it give the desired output given below:

sqlfiddle link

Non exsisting path described above, removed the E from the name : for $i in AuxiliaryObject/Row return <C>{$i}<R>{AuxiliaryObject/@NAM}

code :

    SELECT *
FROM XMLTABLE (
               '<C> {for $i in AuxiliaryObject/Row return <C>{$i}<R>{AuxiliaryObject/@NAM}</R></C>}</C>/C'
               PASSING xmltype(
               '<AuxiliaryType>
                 <AuxiliaryObject id="1" NAME="Provider_P107">
                         <Row>
                              <Index_id>1</Index_id>
                              <Provider_ID_description>GNRCN</Provider_ID_description>
                              <Provider_ID>GNRCN</Provider_ID>
                         </Row>

                          <Row>
                              <Index_id>2</Index_id>
                              <Provider_ID_description>EGUT12</Provider_ID_description>
                              <Provider_ID>EGUT12 </Provider_ID>
                         </Row>
                 </AuxiliaryObject>
                 <AuxiliaryObject id="2" NAME="Provider_P108">
                         <Row>
                              <Index_id>1</Index_id>
                              <Provider_ID_description>GNRCN</Provider_ID_description>
                              <Provider_ID>GNRCN</Provider_ID>
                         </Row>

                          <Row>
                              <Index_id>2</Index_id>
                              <Provider_ID_description>EGUT</Provider_ID_description>
                              <Provider_ID>EGUT </Provider_ID>
                         </Row>
                 </AuxiliaryObject>

                </AuxiliaryType>'
               ).EXTRACT ('AuxiliaryType/*') 
               COLUMNS 
                        Name varchar (30) Path 'R/@NAME',
                        Index_Id VARCHAR2 (10) PATH 'Row/Index_id', 
                       Provider_id_description   VARCHAR2 (30) PATH 'Row/Provider_ID_description',
                       provider_id  VARCHAR2 (30) PATH 'Row/Provider_ID')

Output : Desired :

ID,  Provider_Name,  Index, Provider_ID_description, Provider_ID
 1  Provider_P107     1     GNRCN                   GNRCN
 1  Provider_P107     2     INDF1                   INDF1
 2  Provider_P108     2     EGUT12                  EGUT12
 2  Provider_P108     1     EGUT                    EGUT

Output coming is in the sqlfiddle link.

The above Queation is a link to this : Extract data from a XML and load it into a table

When I run the query on Toad : Output is :

NAME    INDEX_ID    PROVIDER_ID_DESCRIPTION PROVIDER_ID
Provider_P107Provider_P108  1   GNRCN   GNRCN
Provider_P107Provider_P108  2   EGUT12  EGUT12 
Provider_P107Provider_P108  1   GNRCN   GNRCN
Provider_P107Provider_P108  2   EGUT    EGUT

Interesting; it works OK for me in SQL Developer running against an 11.2.0.3 instance on OEL5 - just changing the `NAM` to `NAME` populates the Name column in the output. But in SQL Fiddle the same change gets `ORA-19121: duplicate attribute definition - NAME`. Which patch level (and platform) are you using? — Alex Poole, Jan 03 '14 at 09:25
When I run this on Toad it is giving me result where the first two fields are concatenating also the ID is not coming , updated the result on the Q . — Kimi, Jan 03 '14 at 10:20
The ID isn't coming because you are not selecting it in your query. — Noel, Jan 03 '14 at 10:44
hi , My bad , even after selecting it , the 2 id's are getting concatenated like the NAME — Kimi, Jan 03 '14 at 10:48
@Kimi - yes, I didn't notice they were getting concatenated, I see that too (for name and id). — Alex Poole, Jan 03 '14 at 10:55

Alex Poole · Accepted Answer · 2014-01-03T12:35:21.297

I would extract the data in stages:

SELECT xobjects.id, xobjects.name, xrows.index_id,
  xrows.provider_id_description, xrows.provider_id
FROM XMLTABLE(
    '/AuxiliaryType/AuxiliaryObject'
    PASSING xmltype(
               '<AuxiliaryType>
                 <AuxiliaryObject id="1" NAME="Provider_P107">
                         <Row>
                              <Index_id>1</Index_id>
                              <Provider_ID_description>GNRCN</Provider_ID_description>
                              <Provider_ID>GNRCN</Provider_ID>
                         </Row>

                          <Row>
                              <Index_id>2</Index_id>
                              <Provider_ID_description>EGUT12</Provider_ID_description>
                              <Provider_ID>EGUT12 </Provider_ID>
                         </Row>
                 </AuxiliaryObject>
                 <AuxiliaryObject id="2" NAME="Provider_P108">
                         <Row>
                              <Index_id>1</Index_id>
                              <Provider_ID_description>GNRCN</Provider_ID_description>
                              <Provider_ID>GNRCN</Provider_ID>
                         </Row>

                          <Row>
                              <Index_id>2</Index_id>
                              <Provider_ID_description>EGUT</Provider_ID_description>
                              <Provider_ID>EGUT </Provider_ID>
                         </Row>
                 </AuxiliaryObject>

                </AuxiliaryType>'
    )
    COLUMNS 
    name VARCHAR2(30) PATH '@NAME',
    id VARCHAR2(10) PATH '@id',
    xrows XMLTYPE PATH 'Row') xobjects,
  XMLTABLE(
    '/Row'
    PASSING xobjects.xrows
    COLUMNS
    index_id VARCHAR2(10) PATH 'Index_id', 
    provider_id_description VARCHAR2(30) PATH 'Provider_ID_description',
    provider_id  VARCHAR2(30) PATH 'Provider_ID') xrows;

The XMLTable xobjects contains each of the AuxiliaryObject instances within the AuxiliaryType, from your original XML text. It has the attributes name and id, plus a sub-XMLType containing the nested rows. The second XMLTable, xrows, expands that so the elements can be extracted. The joins and passing of the XML types creates the hierarchy that gives the output you want:

ID         NAME                           INDEX_ID   PROVIDER_ID_DESCRIPTION        PROVIDER_ID                  
---------- ------------------------------ ---------- ------------------------------ ------------------------------
1          Provider_P107                  1          GNRCN                          GNRCN                          
1          Provider_P107                  2          EGUT12                         EGUT12                         
2          Provider_P108                  1          GNRCN                          GNRCN                          
2          Provider_P108                  2          EGUT                           EGUT

This works in SQL Developer against an 11.2.0.3 database, and in SQL Fiddle.

An earlier CTE-based version of this answer also worked in SQL Developer but SQL Fiddle got an ORA-600 error; that along with the issue you had in the question suggests maybe SQL Fiddle is on an unpatched, or at least differently patched, version of 11gR2 which has bugs in the XML handling.

The ORA-600 seems to have be specific to 11.2.0.2 (bug 12381289). I can't see anything in Oracle Support about the ORA-19121, but there are references on the Internet so maybe that's an unpublished bug that was also fixed in 11.2.0.3. Hard to tell. — Alex Poole, Jan 03 '14 at 15:48
In case the xml is very large the the above query is failing with "string literal too long", can such a solution be created using a Query or it has to be done by Java or a coding lang. — Kimi, Jan 07 '14 at 06:56
@Kimi - if it's more than 4k then you need to create the `XMLType` from a `CLOB`, which you easily do from a SQL query. But what is the original source for the text? If it's coming from a file you could load that into a table with a `CLOB` or `XMLType` column and use that directly in the first part of the query; or use a PL/SQL block to read it and construct a temporary `CLOB`. Handling a large input should really a separate question though, it's not related to the error you started this question with. — Alex Poole, Jan 07 '14 at 09:19
The xml is embedded into a table the datatype of that is BLOB. The size is very big it is 250 kb approx. — Kimi, Jan 07 '14 at 12:35
@kimi - so at the moment you do `xmltype()`? That would do an implicit conversion. I think you need [`xmltype.createxml()`](http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/t_xml.htm#ARPLS71959)? Again, if you can't make that work, you should ask a new question showing your current query and what you've tried to change. — Alex Poole, Jan 07 '14 at 12:43
Please find the solution : http://stackoverflow.com/questions/21086074/extracting-from-a-big-xml-the-query-is-failing-with-string-literal-too-long/21086075#21086075 , when working with big xml's — Kimi, Jan 13 '14 at 07:41

Extract xml data using oracle query

1 Answers1

Linked