Oracle_SQL_XML_Fun

xml sql fun

SELECT xmlclobby  FROM (

  select dbms_xmlgen.getxml(Q'[select HIGH_VALUE from SYS.ALL_TAB_PARTITIONS where PARTITION_NAME='PART_INTERVAL_MONTH1']') xmlClobby from dual) ;

--this works!

SELECT XMLTYPE(

         dbms_xmlgen.getxml(Q'[select HIGH_VALUE from SYS.ALL_TAB_PARTITIONS where PARTITION_NAME='PART_INTERVAL_MONTH1']') 

   ).EXTRACT('/ROWSET/ROW/HIGH_VALUE').getClobVal() XMLSrc 

  FROM dual;

--this works also

SELECT XMLTYPE(

         dbms_xmlgen.getxml(Q'[select HIGH_VALUE from SYS.ALL_TAB_PARTITIONS where PARTITION_NAME='PART_INTERVAL_MONTH1']') 

   ).EXTRACT('/ROWSET/ROW/HIGH_VALUE').getStringVal() XMLSrc 

  FROM dual;

--this shows xml as a string

SELECT XMLTYPE(

         dbms_xmlgen.getxml(Q'[select HIGH_VALUE from SYS.ALL_TAB_PARTITIONS where PARTITION_NAME='PART_INTERVAL_MONTH1']') 

   ).EXTRACT('/ROWSET/ROW/HIGH_VALUE').getStringVal() XMLSrc 

  FROM dual;

--this shows the text INSIDE the specified element! (if multiple are returned, they are semicolon separated)

SELECT XMLTYPE(

         dbms_xmlgen.getxml(Q'[select HIGH_VALUE from SYS.ALL_TAB_PARTITIONS where PARTITION_NAME='PART_INTERVAL_MONTH1']') 

   ).EXTRACT('/ROWSET/ROW/HIGH_VALUE/text()').getStringVal() XMLSrc 

FROM dual;

--loop/interate xml nodes via xpath

SELECT extractvalue(column_value, '/user/name') "user"

    FROM TABLE(XMLSequence(

    XMLTYPE('<?xml version="1.0"?><users>

                         <user><name>Mike</name></user>

                         <user><name>Bill</name></user>

                         <user><name>Jill</name></user>

                     </users>').extract('/users/user'))) t;

--loop/interate xml nodes via xpath (get value of an element)

SELECT extractvalue(column_value, 'ROW/HIGH_VALUE') "user"  FROM TABLE(

  XMLSequence(

    XMLTYPE(

         dbms_xmlgen.getxml(Q'[select HIGH_VALUE from SYS.ALL_TAB_PARTITIONS where PARTITION_NAME='PART_INTERVAL_MONTH1']') 

    ).extract( '/ROWSET/ROW')

  )

);