1 2 3 Previous Next 35 Replies Latest reply on Feb 12, 2019 3:46 PM by Mike Kutz

    how to extract  of xmltype data

    heloo

      how to extract all node belong to a3.

      <a1 id='1'>name1</a1>

      <a2>gender</a2>

      <a3>address1</a3>

      <a3 m="2">address2</a3>

      <a3 m="3">address3</a3>

       

      how can I select all a3 node into a cell per record output as below :

      Id    address

      1      <a3>address1</a3><a3 m="2">address2</a3><a3 m="3">address3</a3>

       

      can I use....

      select extract(xmlcolumn, 'id/a3') from table;

        • 1. Re: how to extract  of xmltype data
          John Thorton

          heloo wrote:

           

          how to extract all node belong to a3.

          <a1 id='1'>name1</a1>

          <a2>gender</a2>

          <a3>address1</a3>

          <a3 m="2">address2</a3>

          <a3 m="3">address3</a3>

           

          how can I select all a3 node into a cell per record output as below :

          Id address

          1 <a3>address1</a3><a3 m="2">address2</a3><a3 m="3">address3</a3>

          Please click on URL below & provide details as stated in #5 - #9 inclusive

           

          How do I ask a question on the forums?

          • 2. Re: how to extract  of xmltype data
            cormaco

            Extract and extractvalue are deprecated functions, use xmlquery and xmltable instead:

            with example(xmldata) as (
            select 
            q'[<a1 id='1'>name1</a1>
            <a2>gender</a2>
            <a3>address1</a3>
            <a3 m="2">address2</a3>
            <a3 m="3">address3</a3>]'
            from dual)
            select xmlquery('/a3' passing xmlparse(content xmldata) returning content) as result from example
            
            RESULT                                                                          
            --------------------------------------------------------------------------------
            <a3>address1</a3><a3 m="2">address2</a3><a3 m="3">address3</a3>
            
            
            
            
            • 3. Re: how to extract  of xmltype data
              heloo

              how to all the record in table which  <a3 m='2"> = 'address2'?

              • 4. Re: how to extract  of xmltype data
                heloo

                i got this error when i try "select xmlquery('/a3' passing xmlparse(content xmlcolumn) returning content) as result from table"

                 

                ExampleExceptionFormatter: exception message was: ORA-00932: inconsistent datatypes: expected CHAR got -

                • 5. Re: how to extract  of xmltype data
                  cormaco

                  how to all the record in table which  <a3 m='2"> = 'address2'?

                   

                  Like this:

                  select xmlquery('/a3[@m=2 and .="address2"]' passing xmlparse(content xmldata) returning content) as result from example  
                  
                  
                  RESULT                                                                          
                  --------------------------------------------------------------------------------
                  <a3 m="2">address2</a3>
                  
                  
                  
                  • 6. Re: how to extract  of xmltype data
                    cormaco

                    heloo schrieb:

                     

                    i got this error when i try "select xmlquery('/a3' passing xmlparse(content xmlcolumn) returning content) as result from table"

                     

                    ExampleExceptionFormatter: exception message was: ORA-00932: inconsistent datatypes: expected CHAR got -

                    What did you pass as xmlcolumn? What is you database version? I used Oracle 18XE.

                    • 7. Re: how to extract  of xmltype data
                      heloo

                      Oracle 12c

                      • 8. Re: how to extract  of xmltype data
                        Paulzip

                        heloo wrote:

                         

                        i got this error when i try "select xmlquery('/a3' passing xmlparse(content xmlcolumn) returning content) as result from table"

                         

                        ExampleExceptionFormatter: exception message was: ORA-00932: inconsistent datatypes: expected CHAR got -

                        This assumes you have a table called "table" which has a column called "xmlcolumn" which is character data (varchar2, clob etc).

                        • 9. Re: how to extract  of xmltype data
                          heloo

                          This assumes you have a table called "table" which has a column called "xmlcolumn" which is character data (varchar2, clob etc). <-- xmlcolumn defined as xmltype,

                          i just know "xmlcolumn" is xmltype, how can i check which is varchar2,clob or not?

                          • 10. Re: how to extract  of xmltype data
                            cormaco

                            This assumes you have a table called "table" which has a column called "xmlcolumn" which is character data (varchar2, clob etc). <-- xmlcolumn defined as xmltype,

                            If your xmlcolumn is already xmltype, then you don't need to call xmlparse:

                            select xmlquery('/a3' passing xmlcolumn as result from table
                            
                            • 11. Re: how to extract  of xmltype data
                              heloo

                              it is not successful, it returns null for all record.

                               

                              select xmlquery('/a3' passing xmlcolumn returning content) as result from table

                              • 12. Re: how to extract  of xmltype data
                                cormaco

                                heloo schrieb:

                                 

                                it is not successful, it returns null for all record.

                                 

                                select xmlquery('/a3' passing xmlcolumn returning content) as result from table

                                Then post an example of your actual data

                                • 13. Re: how to extract  of xmltype data
                                  Gaz in Oz

                                  If your example data is like the other posts you have posted about XML, then here's an example, based on one of @cormaco's posts:

                                  SQL> describe your_xml
                                   Name                                      Null?    Type
                                   ----------------------------------------- -------- -----------------------------
                                   ID                                                 NUMBER(38)
                                   XMLTYPECOLUMN                                      PUBLIC.XMLTYPE STORAGE BINARY
                                  
                                  SQL> ed
                                  Wrote file afiedt.buf
                                  
                                    1  select id,
                                    2         xmlquery('/row/a3' passing t.xmltypecolumn returning content) as result
                                    3* from   your_xml t
                                  SQL> /
                                  
                                          ID RESULT
                                  ---------- ------------------------------------------------------------
                                           1 <a3>tel1</a3>
                                             <a3 m="2">20181231</a3>
                                             <a3 m="3">date3</a3>
                                  
                                           2 <a3>tel2</a3>
                                             <a3 m="3">date2</a3>
                                  
                                           3 <a3>tel3</a3>
                                             <a3 m="2">20181231</a3>
                                             <a3 m="3">date4</a3>
                                  
                                  
                                  3 rows selected.
                                  
                                  SQL>
                                  
                                  • 14. Re: how to extract  of xmltype data
                                    Gaz in Oz

                                    ...and if it is the values you want then xmltable() can do that, for example:

                                    SQL> ed
                                    Wrote file afiedt.buf
                                    
                                     1  select x.*
                                     2  from your_xml t,
                                     3       xmltable('/row'
                                     4                passing xmltypecolumn
                                     5                columns id    NUMBER      PATH '@id',
                                     6                        a1    VARCHAR2(9) PATH 'a1',
                                     7                        a2    VARCHAR2(9) PATH 'a2',
                                     8                        a3_1  VARCHAR2(9) PATH 'a3[1]',
                                     9                        a3_1m VARCHAR2(5) PATH 'a3[1]/@m',
                                    10                        a3_2  VARCHAR2(9) PATH 'a3[2]',
                                    11                        a3_2m VARCHAR2(5) PATH 'a3[2]/@m',
                                    12                        a3_3  VARCHAR2(9) PATH 'a3[3]'
                                    13*      ) x
                                    SQL> /
                                    
                                            ID A1        A2        A3_1      A3_1M A3_2      A3_2M A3_3
                                    ---------- --------- --------- --------- ----- --------- ----- ---------
                                             1 name      address1  tel1      2    20181231  3    date3
                                             1 name2     address2  tel2      3    date2
                                             1 name3     address3  tel3      2    20181231  3    date4
                                    
                                    3 rows selected.
                                    
                                    SQL>
                                    
                                    1 2 3 Previous Next