title: tabular data from pivot xml query oracle
body: <p>I have following Oracle sql query written using "pivot xml" clause</p>
  
  <pre><code>SELECT *
     FROM
    (SELECT tid,item,
      current_value
    FROM
      (SELECT rownum,
       last_value(tid ignore nulls) over (order by rownum) tid,
        last_value(category ignore nulls) over (order by rownum) category,
        last_value(item ignore nulls) over (order by rownum) item,
        current_value
      FROM pi_perfdata_new
      ORDER BY rownum
      )
    WHERE category IN ('ClassA','ClassB') and tid is not null
    ) pivot xml (max(current_value) FOR item IN (any ));
  </code></pre>
  
  <p>This gives output in following format:</p>
  
  <pre><code>TID  ITEM_XML 
  
  101 &lt;PivotSet&gt;&lt;item&gt;&lt;column name = "ITEM"&gt;MID&lt;/column&gt;&lt;column name = "MAX(CURRENT_VALUE)"&gt;C009&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "ITEM"&gt;PNAME&lt;/column&gt;&lt;column name = "MAX(CURRENT_VALUE)"&gt;XYZ CO.&lt;/column&gt;&lt;/item&gt;&lt;/PivotSet&gt;
  102 &lt;PivotSet&gt;&lt;item&gt;&lt;column name = "ITEM"&gt;MID&lt;/column&gt;&lt;column name = "MAX(CURRENT_VALUE)"&gt;C011&lt;/column&gt;&lt;/item&gt;&lt;item&gt;&lt;column name = "ITEM"&gt;PNAME&lt;/column&gt;&lt;column name = "MAX(CURRENT_VALUE)"&gt;LMN CO.&lt;/column&gt;&lt;/item&gt;&lt;/PivotSet&gt;
  </code></pre>
  
  <p>Now I need to convert the above XML data in tabular format below:</p>
  
  <pre><code>MID   PNAME
  c009  XYZ CO. 
  c011  LMN CO.
  </code></pre>
  
  <p>I tried something like below but that doesnot gives me desired o/p as above</p>
  
  <pre><code>  WITH pivot_info AS  (SELECT *   FROM   (SELECT tid,   item,  current_value
  FROM
    (SELECT rownum,
      last_value(tid ignore nulls) over (order by rownum) tid,
      last_value(category ignore nulls) over (order by rownum) category,
      last_value(item ignore nulls) over (order by rownum) item,
      current_value
    FROM pi_perfdata_new
    ORDER BY rownum
    )
  WHERE category                           IN ('ClassA','ClassB')
  AND tid                                  IS NOT NULL
  ) pivot xml (MAX(current_value) FOR item IN (ANY ))  ) SELECT extractvalue (value (t), '/item/column[1]') item ,  extractvalue (value (t), '/item/column[2]') value FROM pivot_info,  XMLTable ('/PivotSet/item' passing item_xml) t ;
  </code></pre>
  
  <p>---Above sql gives o/p in following format</p>
  
  <pre><code>item value
  MID   C009
  PNAME XYZ CO.
  MID   C011
  PNAME LMN CO.  
  </code></pre>
  
  <p>But I need data in following tabular format;</p>
  
  <pre><code>MID   PNAME
  c009  XYZ CO. 
  c011  LMN CO.
  </code></pre>
  
  <p>Please suggest.</p>