title: Oracle XMLTYPE extract root prolog value
body: <pre><code>SELECT * FROM v$version;
  *Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  PL/SQL Release 12.1.0.2.0 - Production
  &quot;CORE   12.1.0.2.0  Production&quot;
  TNS for Linux: Version 12.1.0.2.0 - Production
  NLSRTL Version 12.1.0.2.0 - Production*
  </code></pre>
  <p>I have sample Query with XML like below:</p>
  <pre><code>with t(xml) as 
  (
  select xmltype(
  '&lt;SSO_XML
      xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot;
      xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot; 
  TimeStamp=&quot;2020-08-05T21:57:23Z&quot; 
  Target=&quot;Production&quot; 
  Version=&quot;1.0&quot; 
  TransactionIdentifier=&quot;PLAN_A&quot; 
  SequenceNmbr=&quot;123456&quot;
      xmlns=&quot;http://www.w3.org/2001/XMLSchema&quot;&gt;
      &lt;PlanCode PlanCodeCode=&quot;CHOICE&quot;&gt;
          &lt;S_DAYS FARE=&quot;10&quot; Start=&quot;2020-08-07&quot; End=&quot;2020-10-30&quot; Mon=&quot;true&quot; Tue=&quot;true&quot; Weds=&quot;true&quot; Thur=&quot;true&quot; Fri=&quot;true&quot; Sat=&quot;true&quot; Sun=&quot;true&quot;&gt;
              &lt;STUDENT&gt;
                  &lt;DIVISION ORIGINAL=&quot;150.05&quot; Code=&quot;Flat&quot; S_CODE=&quot;1&quot; /&gt;
                  &lt;DIVISION ORIGINAL=&quot;150.05&quot; Code=&quot;Flat&quot; S_CODE=&quot;2&quot; /&gt;
              &lt;/STUDENT&gt;
          &lt;/S_DAYS&gt;
      &lt;/PlanCode&gt;
  &lt;/SSO_XML&gt;') 
   from dual
   )
  
  select h.PlanCodeCode
  ,b.Original
  ,b.code
  ,b.s_code
   from   t
      cross join
      xmltable(xmlnamespaces(default 'http://www.w3.org/2001/XMLSchema'),
               '/SSO_XML'
               passing t.xml
               columns PlanCodeCode varchar2(100)  path './PlanCode/@PlanCodeCode',
                       attributes xmltype path './PlanCode'
              ) h
      cross join xmltable(xmlnamespaces(default 'http://www.w3.org/2001/XMLSchema'),
               'PlanCode/S_DAYS/STUDENT/DIVISION'
               passing h.attributes
               columns 
                      ORIGINAL number path  '@ORIGINAL',
                      Code            varchar2(100) path '@Code',
                      S_CODE  number path '@S_CODE'
              ) b;
  </code></pre>
  <p>Output:</p>
  <p><a href="https://i.stack.imgur.com/EKt89.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EKt89.png" alt="enter image description here" /></a></p>
  <p>I am trying to fetch the values below within the query or a new query:
  Is there any way to do that? Any help  / direction appreciated.</p>
  <p><a href="https://i.stack.imgur.com/JFR8t.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JFR8t.png" alt="enter image description here" /></a></p>