9 Replies Latest reply on Sep 11, 2007 5:59 AM by MichaelS

    Obtain node name

    595065
      Hi, i wonder if you could please help me with this.
      I want to obtain the name of each node in this xml source:

      <Objects>
      <Label ID="lblTest">Test</Label>
      <Label>Another Test</Label>
      <Text ID="txtTest">Text Test</Text>
      </Objects>

      Because i don't want to reference them for its name i was using this xpath to get 'em one by one: /Objects/node()[1], /Objects/node()[2]... and so on.

      My question is: is there a expression like node(), attribute(), text(), @name, @node or something like that returns what i want?.

      Was trying to get the result i want by processing the node as a string with a function called get_node_name:

      DECLARE
      lxml_objects xmltype DEFAULT xmltype('
      <Objects>
      <Label ID="lblTest">Test</Label>
      <Label>Another Test</Label>
      <Text ID="txtTest">Text Test</Text>
      </Objects>
      ');
      lvc2_xpath VARCHAR2(100) DEFAULT '/Objects/node()[:lint_counter]';
      lint_counter PLS_INTEGER DEFAULT 1;

      -- Function to get the node name
      FUNCTION get_node_name(lxml_node IN xmltype) RETURN VARCHAR2 IS
      lvc2_node_name VARCHAR2(30) DEFAULT REPLACE(substr(lxml_node.getstringval,1,instr(lxml_node.getstringval,'>')),'<');
      lint_end_of_name PLS_INTEGER DEFAULT instr(lvc2_node_name,' ');
      BEGIN
      IF NOT lint_end_of_name > 0 THEN
      lint_end_of_name := length(lvc2_node_name);
      END IF;
      RETURN substr(lvc2_node_name,1,lint_end_of_name - 1);
      END;

      BEGIN
      WHILE lxml_objects.existsnode(REPLACE(lvc2_xpath,':lint_counter',lint_counter)) = 1
      LOOP
      dbms_output.put_line('Node ' || to_char(lint_counter) || ' is ' || get_node_name(lxml_objects.extract(REPLACE(lvc2_xpath,':lint_counter',lint_counter))));
      lint_counter := lint_counter + 1;
      END LOOP;
      END;

      The results are:

      Node 1 is Label
      Node 2 is Label
      Node 3 is Text

      ---

      Thank you in advance for your help,
      Regards.
        • 1. Re: Obtain node name
          MichaelS
          This?:
          michaels>  with t as 
          (
          select xmltype('<Objects>
                            <Label ID="lblTest">Test</Label>
                            <Label>Another Test</Label>
                            <Text ID="txtTest">Text Test</Text>
                          </Objects>') xml 
           from dual
          )
          ---
          ---
          select t2.column_value.getrootelement() nodes
            from t t, table(xmlsequence(t.xml.extract('//node()'))) t2
          
          NODES                         
          ------------------------------
          Objects                       
          Label                         
          Label                         
          Text                          
          
          
          4 rows selected.
          • 2. Re: Obtain node name
            595065
            Yes, thank you very much.
            i certainly obviated the getrootelement() function and that shorter way to do it with with sql.
            thanks a lot,
            Regards.
            • 3. Re: Obtain node name
              Marco Gralike
              Based on Christian Antognini's remarks here (http://www.freelists.org/archives/oracle-l/12-2005/msg00133.html), I deducted the following alternatives...
              -- looks like that this is faster than the new WITH AS () SELECT FROM contruct
              
              SQL> select t.object_value.getrootelement() as "NODES"
                2    from
                3    XMLTABLE('//*'
                4             passing xmltype('<Objects>
                5                                <Label ID="lblTest">Test</Label>
                6                                <Label>Another Test</Label>
                7                                <Text ID="txtTest">Text Test</Text>
                8                             </Objects>')
                9            ) t;
              
              NODES
              --------------------------------------------------
              Objects
              Label
              Label
              Text
              Apparently the by Chris mentioned //name() method is working with XMLTABLE (but probably not supported) in an 11gR1 database
              SQL> select t.object_value as "NODES"
                2    from
                3    XMLTABLE('//name()'
                4             passing xmltype('<Objects>
                5                                <Label ID="lblTest">Test</Label>
                6                                <Label>Another Test</Label>
                7                                <Text ID="txtTest">Text Test</Text>
                8                             </Objects>')
                9            ) t;
              
              NODES
              --------------------------------------------------
              
              Objects
              Label
              
              Label
              
              Text
              
              
              8 rows selected. -- <== !!
              
              SQL> with t as
                2  (
                3  select xmltype('<Objects>
                4                    <Label ID="lblTest">Test</Label>
                5                    <Label>Another Test</Label>
                6                    <Text ID="txtTest">Text Test</Text>
                7                  </Objects>') xml
                8   from dual
                9  )
               10  ---
               11  ---
               12  select t2.column_value as "NODES"
               13    from t t, table(xmlsequence(t.xml.extract('//name()'))) t2
               14  ;
              ERROR:
              ORA-31011: XML parsing failed
              ORA-19202: Error occurred in XML processing
              LPX-00601: Invalid token in: '//name()'
              
              no rows selected
              Message was edited by:
              Marco Gralike
              • 4. Re: Obtain node name
                595065
                Hi again, i have one more question please.
                I tried your code and works fine, but, can i use this XPath: /Objects/node(), instead of : //node() ?, to obtain only one level of the tree as result?.

                Changed it and got the results i needed, is it valid?, or it involves any others changes or variations in the results?, because when i change my xml source to this:
                <Objects>
                  <Label ID="lblTest">Test</Label>
                </Objects>
                where there's only one element, at the moment of running it, i'm getting one result, but it's a NULL.
                SQL> with t as
                  2  (
                  3  select xmltype('<Objects>
                  4                    <Label ID="lblTest">Test</Label>
                  5                  </Objects>') xml
                  6   from dual
                  7  )
                  8  ---
                  9  ---
                10  select t2.column_value.getrootelement() nodes
                11    from t t, table(xmlsequence(t.xml.extract('/Objects/node()'))) t2
                12  /

                NODES
                --------------------------------------------------------------------------------
                Could you please tell me why is that or what am i doing wrong?.

                Thank you,
                Regards
                • 5. Re: Obtain node name
                  Marco Gralike
                  What Mr. Michels was demonstration did was that it showed the ROOT element of every result XML instance. Because the example cycled / traversed through every level using a table(xmlsequence(t.xml.extract())) construct, the result doesn't contain an /Objects/Label.

                  Have a try with the next select to see the difference
                  select t2.*
                  from t t, table(xmlsequence(t.xml.extract('/Objects/node()'))) t2
                  http://www.w3.org/TR/xpath
                  http://www.w3schools.com/xpath/xpath_axes.asp
                  child::node() - Selects all child nodes of the current node 
                  Message was edited by:
                  Marco Gralike
                  • 6. Re: Obtain node name
                    595065
                    Ok, i see, //node() returns all children nodes, cycling through all levels, it is the abbreviation of descendant::node() right?.

                    The problem is that i just need to get the first level of childen from Objects, using /Objects/node() returns all the first level children.
                    SQL> with t as
                      2  (
                      3  select xmltype('
                      4  <Objects>
                      5     <Label>Another Test</Label>
                      6  </Objects>') xml
                      7  from dual
                      8  )
                      9  select t2.*
                    10  from t t, table(xmlsequence(t.xml.extract('/Objects/node()'))) t2;

                    COLUMN_VALUE
                    --------------------------------------------------------------------------------
                    <Label>Another Test</Label>
                    Why does it return NULL when i apply the function getrootelement() to <Label>Another Test</Label> ?

                    shouldn't it return Label ?, if not, is there any function that returns the node name?.

                    Thank you for your answers,
                    Regards
                    • 7. Re: Obtain node name
                      MaximDemenko
                      Marco, i am not sure to have understood your explanation...
                      SQL> with t as
                        2  (
                        3  select xmltype('<Objects>
                        4                    <Label ID="lblTest">Test</Label>
                        5                  </Objects>') xml
                        6   from dual
                        7  )
                        8  ---
                        9  ---
                       10  select t2.column_value,
                       11  xmltype(CAST (t2.column_value AS VARCHAR2(4000))).getrootelement() root
                       12  from t t, table(xmlsequence(t.xml.extract('/Objects/node()'))) t2
                       13  /
                      
                      COLUMN_VALUE                             ROOT
                      ---------------------------------------- ----------------------------------------
                      <Label ID="lblTest">Test</Label>         Label
                      Best regards

                      Maxim
                      • 8. Re: Obtain node name
                        Marco Gralike
                        with t as 
                        (
                        select xmltype('<Objects>
                                          <Label ID="lblTest">Test</Label>
                                          <Label>Another Test</Label>
                                          <Text ID="txtTest">Text Test</Text>
                                        </Objects>') xml 
                         from dual
                        )
                        ---
                        ---
                        select t2.column_value
                          from t t, table(xmlsequence(t.xml.extract('//node()'))) t2
                        Doing this by hart /don't have a database at hand /its late here in europe (amsterdam et all)

                        If you tried the above, you will get different sections of fragments. This is caused by the table(xmlsequence()). The getrootelement() was ment for returning a root element. While feeding it with traversed sections of the whole,it returns in this case the parts that we want.

                        Can't give you a better example now, as said,its late and no database at hand to better demonstrate (but I think you have given an good example anyway)
                        • 9. Re: Obtain node name
                          MichaelS
                          Why does it return NULL when i apply the function getrootelement() to <Label>Another Test</Label> ?
                          I would consider this as a bug: As Maxim nicely demonstrates if you CAST the resulting XMLType to VARCHAR2 and then back again to XMLType and then apply getrootelement on it you'll get the nodename back.

                          So considering
                          select t2.column_value c1, 
                                 xmltype (t2.column_value.getstringval ()) c2
                            from t t, table (xmlsequence (t.xml.extract ('Objects/node()'))) t2
                          If you now apply getrootelement to both c1 and c2 you'll get NULL in the first case but the real nodename (Label) in the second case eventhough both columns have exactly the same content and datatype. Looks as a bug to me.

                          Regards
                          Michael