12 Replies Latest reply: Mar 14, 2013 6:02 AM by 946279 RSS

    to retrieve data from xml datatype

    974168
      hi ...
      i have one doubt in oracle database .. that is how to to retrieve data from xml datatype ??????
        • 1. Re: to retrieve data from xml datatype
          971895
          we can do it.
          • 2. Re: to retrieve data from xml datatype
            BluShadow
            What is the issue you are having exactly?

            Please read: {message:id=9360002}

            Here's a simple example of reading XML data from an XMLTYPE datatype...
            SQL> ed
            Wrote file afiedt.buf
            
              1  WITH xml_table AS
              2      (SELECT xmltype('
              3      <root>
              4        <child name="name1">
              5          <grandchild name="name11">
              6            <greatgrandchild name="name111"/>
              7            <greatgrandchild name="name112"/>
              8          </grandchild>
              9          <grandchild name="name12"/>
             10       </child>
             11       <child name="name2">
             12         <grandchild name="name21"/>
             13            <greatgrandchild name="name211"/>
             14         <grandchild name="name22"/>
             15       </child>
             16     </root>') object_value FROM dual)
             17     select po.child_name, gc.gchild_name, ggc.ggchild_name
             18     from   xml_table p
             19           ,xmltable('/root/child'
             20                     passing p.object_value
             21                     columns child_name  varchar2(100) path '@name'
             22                           ,gchild      xmltype       path 'grandchild'
             23                    ) po
             24           ,xmltable('/grandchild'
             25                     passing po.gchild
             26                     columns gchild_name varchar2(100) path '@name'
             27                            ,ggchild     xmltype       path 'greatgrandchild'
             28                   ) (+) gc
             29           ,xmltable('/greatgrandchild'
             30                     passing gc.ggchild
             31                     columns ggchild_name varchar2(100) path '@name'
             32*                  ) (+) ggc
            SQL> /
            
            CHILD_NAME           GCHILD_NAME          GGCHILD_NAME
            -------------------- -------------------- --------------------
            name1                name11               name111
            name1                name11               name112
            name1                name12
            name2                name21
            name2                name22
            
            SQL>
            However, there are many methods depending on what you are trying to achieve.

            There is also an XML DB forum which is dedicated to XML handling in Oracle databases... and it has a FAQ of it's own with plenty of useful information:

            {thread:id=410714}
            • 3. Re: to retrieve data from xml datatype
              974168
              how u do it?? plz explain


              <?xml version="1.0"?>
              <DETAILS>
              <FIELDS_VALUES>
              <FIELD><NAME>toMonth</NAME><VALUE>5</VALUE></FIELD>
              <FIELD><NAME>ctTestPan</NAME><VALUE>1</VALUE></FIELD>
              <FIELD><NAME>costType</NAME><VALUE>2</VALUE></FIELD>
              <FIELD><NAME>toYear</NAME><VALUE>2012</VALUE></FIELD>
              <FIELD><NAME>fromMonth</NAME><VALUE>12</VALUE></FIELD>
              <FIELD><NAME>fromYear</NAME><VALUE>2011</VALUE></FIELD>
              </FIELDS_VALUES>
              </DETAILS>

              its the xml file.. i want retrieve values from above xml file using sql script..
              • 4. Re: to retrieve data from xml datatype
                BluShadow
                968892 wrote:
                we can do it.
                Such a helpful answer. I bet the OP is glad you've helped him so much.
                • 5. Re: to retrieve data from xml datatype
                  974168
                  i cant get anything from that ..plz explain based on my xml file..
                  • 6. Re: to retrieve data from xml datatype
                    BluShadow
                    Like this...
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  with t as (select xmltype('<?xml version="1.0"?>
                      2  <DETAILS>
                      3    <FIELDS_VALUES>
                      4      <FIELD><NAME>toMonth</NAME><VALUE>5</VALUE></FIELD>
                      5      <FIELD><NAME>ctTestPan</NAME><VALUE>1</VALUE></FIELD>
                      6      <FIELD><NAME>costType</NAME><VALUE>2</VALUE></FIELD>
                      7      <FIELD><NAME>toYear</NAME><VALUE>2012</VALUE></FIELD>
                      8      <FIELD><NAME>fromMonth</NAME><VALUE>12</VALUE></FIELD>
                      9      <FIELD><NAME>fromYear</NAME><VALUE>2011</VALUE></FIELD>
                     10    </FIELDS_VALUES>
                     11  </DETAILS>') as xml from dual)
                     12  --
                     13  -- end of sample XMLDATA, use below query on your own table etc. as required
                     14  --
                     15  select x.*
                     16  from   t
                     17        ,xmltable('/DETAILS/FIELDS_VALUES/FIELD'
                     18                  passing t.xml
                     19                  columns name varchar2(30) path './NAME'
                     20                         ,val  varchar2(10) path './VALUE'
                     21*                ) x
                    SQL> /
                    
                    NAME                           VAL
                    ------------------------------ ----------
                    toMonth                        5
                    ctTestPan                      1
                    costType                       2
                    toYear                         2012
                    fromMonth                      12
                    fromYear                       2011
                    
                    6 rows selected.
                    • 7. Re: to retrieve data from xml datatype
                      974168
                      oracle.sql.OPAQUE@4e1a77


                      This is the XML data that is stored in the field of oracle XML dataype .So from this oracle.sql.OPAQUE@4e1a77 ..how can i retrieve the actual data
                      • 8. Re: to retrieve data from xml datatype
                        BluShadow
                        971165 wrote:
                        oracle.sql.OPAQUE@4e1a77


                        This is the XML data that is stored in the field of oracle XML dataype .So from this oracle.sql.OPAQUE@4e1a77 ..how can i retrieve the actual data
                        Huh?

                        That is NOT xml data.

                        Please explain your problem as described in the FAQ: {message:id=9360002}

                        If you fail to ask your questions properly, you will fail to get good answers.
                        • 9. Re: to retrieve data from xml datatype
                          974168
                          ok ...
                          • 10. Re: to retrieve data from xml datatype
                            946279
                            why in your example results there is no greatgrandchild 211?

                            thank you
                            • 11. Re: to retrieve data from xml datatype
                              BluShadow
                              943276 wrote:
                              why in your example results there is no greatgrandchild 211?

                              thank you
                              Because of an error in the XML itself. The greatgrandchild was not nested within the grandchild (it's parent)

                              This is correct:
                              SQL> ed
                              Wrote file afiedt.buf
                              
                                1  WITH xml_table AS
                                2      (SELECT xmltype('
                                3       <root>
                                4         <child name="name1">
                                5           <grandchild name="name11">
                                6             <greatgrandchild name="name111"/>
                                7             <greatgrandchild name="name112"/>
                                8           </grandchild>
                                9           <grandchild name="name12"/>
                               10         </child>
                               11         <child name="name2">
                               12           <grandchild name="name21">
                               13             <greatgrandchild name="name211"/>
                               14           </grandchild>
                               15           <grandchild name="name22"/>
                               16         </child>
                               17       </root>') object_value FROM dual)
                               18  select po.child_name, gc.gchild_name, ggc.ggchild_name
                               19  from   xml_table p
                               20        ,xmltable('/root/child'
                               21                  passing p.object_value
                               22                  columns child_name  varchar2(100) path '@name'
                               23                        ,gchild      xmltype       path 'grandchild'
                               24                 ) po
                               25        ,xmltable('/grandchild'
                               26                  passing po.gchild
                               27                  columns gchild_name varchar2(100) path '@name'
                               28                         ,ggchild     xmltype       path 'greatgrandchild'
                               29                 ) (+) gc
                               30        ,xmltable('/greatgrandchild'
                               31                  passing gc.ggchild
                               32                  columns ggchild_name varchar2(100) path '@name'
                               33*                ) (+) ggc
                              SQL> /
                              
                              CHILD_NAME           GCHILD_NAME          GGCHILD_NAME
                              -------------------- -------------------- --------------------
                              name1                name11               name111
                              name1                name11               name112
                              name1                name12
                              name2                name21               name211
                              name2                name22
                              • 12. Re: to retrieve data from xml datatype
                                946279
                                thank you!