1 Reply Latest reply: Mar 19, 2013 5:22 AM by AlexAnd RSS

    Read XML tags

    Jitesh Gurnani-Oracle
      Hi,

      I have the below XML

      <RESULTS>
      <ROW>
      <COLUMN NAME="ID">12688f8ac8aa6310VgnVCM10000078ccc70a____</COLUMN>
      <COLUMN NAME="BRANCH_NAME">Brooklyn</COLUMN>
      </ROW>
      </RESULTS>


      Could somebody please assist me with a way to read the XML tags alone from this. We do not require the data as such.

      A query on this XML should return

      RESULTS
      ROW
      COLUMN NAME and so on ...

      Regards,
      Jitesh
        • 1. Re: Read XML tags
          AlexAnd
          forum has many examples
          like Re: multiple xml tag values are not getting in output.
          Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
          Connected as apps
           
          SQL> 
          SQL> with t as
            2   (select xmltype('<RESULTS>
            3  <ROW>
            4  <COLUMN NAME="ID">12688f8ac8aa6310VgnVCM10000078ccc70a____</COLUMN>
            5  <COLUMN NAME="BRANCH_NAME">Brooklyn</COLUMN>
            6  </ROW>
            7  </RESULTS>') xml
            8      from dual)
            9  select x.*
           10    from t,
           11         xmltable('for $i in $d/descendant::*
           12             return element r {
           13               element tag_name    {local-name($i)}, element tag_name2 {name($i)}
           14            }' passing t.xml as "d" columns tag_name
           15                  varchar2(30) path 'tag_name',
           16                  tag_name2 varchar2(30) path 'tag_name2') x
           17  /
           
          TAG_NAME                       TAG_NAME2
          ------------------------------ ------------------------------
          RESULTS                        RESULTS
          ROW                            ROW
          COLUMN                         COLUMN
          COLUMN                         COLUMN
           
          SQL>