EXTRACAO_XML_ORACLE

SELECT SYS.XMLTYPE.createxml('<FAQ-LIST>
                <FAQ-ID>01</FAQ-ID>
                <QUESTION>
                     <QUERY>Question 1</QUERY>
                     <RESPONSE>Answer goes here.</RESPONSE>
                </QUESTION>
                <QUESTION>
                     <QUERY>Question 2</QUERY>
                     <RESPONSE>Answer goes here.</RESPONSE>
                </QUESTION>
                <QUESTION>
                     <QUERY>Question 3</QUERY>
                     <RESPONSE>Answer goes here.</RESPONSE>
                </QUESTION>
             </FAQ-LIST>') FROM DUAL



SELECT * FROM
XMLTABLE ('/FAQ-LIST/QUESTION'
                    PASSING (SYS.XMLTYPE.createxml('<FAQ-LIST>
                <FAQ-ID>01</FAQ-ID>
                <QUESTION>
                     <QUERY>Question 1</QUERY>
                     <RESPONSE>Answer goes here.</RESPONSE>
                </QUESTION>
                <QUESTION>
                     <QUERY>Question 2</QUERY>
                     <RESPONSE>Answer goes here.</RESPONSE>
                </QUESTION>
                <QUESTION>
                     <QUERY>Question 3</QUERY>
                     <RESPONSE>Answer goes here.</RESPONSE>
                </QUESTION>
             </FAQ-LIST>'))
                    COLUMNS Question VARCHAR2(30) PATH 'QUERY',
                            RESPONSE VARCHAR2(30) PATH 'RESPONSE') x