7 Replies Latest reply: Mar 25, 2013 3:30 PM by 838673 RSS

    extract root name of an XML packet

    838673
      Hi,
      I thought this would be simple, but cant work out how I can extract the name of the root node for an XML packet
      I was planning just to use xmltype.extract, but I cant work out the xpath paameter to get this to work.
      Any of you clever folks got an idea?
        • 1. Re: extract root name of an XML packet
          AlexAnd
          how I can extract the name of the root node for an XML packet
          try
          Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
          Connected as scott
           
          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/*
           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
           
          SQL> 
          • 2. Re: extract root name of an XML packet
            838673
            Thanks for this: just trying to understand it.
            I am using PLSQL.
            I have the XML in an XMLtype variable e.g. var_XML
            I want to use something like this to extract the root node:
            xmltype.extract(var_XML, '?????').getStringVal()
            Using the example packet from your reply above, what string could I use to extract the parent node name RESULTS from the XML
            • 3. Re: extract root name of an XML packet
              AlexAnd
              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 t.xml.getRootElement() from t t
               10  /
               
              T.XML.GETROOTELEMENT()
              --------------------------------------------------------------------------------
              RESULTS
               
              SQL> 
              or
              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 xmltype.getRootElement(t.xml) from t
               10  /
               
              XMLTYPE.GETROOTELEMENT(T.XML)
              --------------------------------------------------------------------------------
              RESULTS
               
              SQL> 
              • 4. Re: extract root name of an XML packet
                838673
                That's great thanks, and I can certainly use this. I may have to do this many times in a job. Is there a way of doing it in pure PLSQL, without having the overhead of a context switch with the SQL engine?
                • 5. Re: extract root name of an XML packet
                  AlexAnd
                  Is there a way of doing it in pure PLSQL, without having the overhead of a context switch with the SQL engine?
                  may be this
                  SQL> declare
                    2  
                    3  t xmltype:=xmltype('<RESULTS>
                    4  <ROW>
                    5  <COLUMN NAME="ID">12688f8ac8aa6310VgnVCM10000078ccc70a____</COLUMN>
                    6  <COLUMN NAME="BRANCH_NAME">Brooklyn</COLUMN>
                    7  </ROW>
                    8  </RESULTS>');
                    9  
                   10  begin
                   11  
                   12  dbms_output.put_line('1-' || xmltype.getRootElement(t));
                   13  dbms_output.put_line('2-' || t.getRootElement());
                   14  
                   15  end;
                   16  /
                   
                  1-RESULTS
                  2-RESULTS
                   
                  PL/SQL procedure successfully completed
                   
                  SQL> 
                  • 6. Re: extract root name of an XML packet
                    Jason_(A_Non)
                    You mean like
                    declare 
                      l_root    varchar2(20);
                      l_xmltype xmltype;
                    begin
                       l_xmltype := XMLTYPE('<RESULTS>
                    <ROW>
                    <COLUMN NAME="ID">12688f8ac8aa6310VgnVCM10000078ccc70a____</COLUMN>
                    <COLUMN NAME="BRANCH_NAME">Brooklyn</COLUMN>
                    </ROW>
                    </RESULTS>');
                      l_root := l_xmltype.getRootElement();
                      dbms_output.put_line(l_root);
                      
                    end;
                    • 7. Re: extract root name of an XML packet
                      838673
                      That's great thankyou