Retorno de Select em formato XML

[jotaod]

BOm dia,

No MSSQL existe uma instrução na qual retornamos um XML como resultado de uma select, como por exemplo:

Selecionar tudo

select * from table FOR XML AUTO

Estou precisando fazer o mesmo com algumas select´s em PLSQL.

Alguém saberia me dizer como posso fazer isso?

Obrigado

Jota

[Trevisolli]

Brother, automaticamente sei que tem SIM.
Só não tive tempo de dar uma olhada mais a fundo.

Abaixo vai um exemplo feito na "mão":

Selecionar tudo

DECLARE
 CURSOR cur_Registro
   IS 
	  SELECT *
	    FROM dept; 
  rec_registro cur_registro%ROWTYPE;	    
BEGIN 
  OPEN cur_registro;
  LOOP 
    FETCH cur_registro INTO rec_registro;
    EXIT WHEN cur_registro%NOTFOUND;
    dbms_output.put_line('<Registro>');
    dbms_output.put_line('  <Seq>' ||  (cur_registro%ROWCOUNT) );
    dbms_output.put_line('    <Depto> ' || rec_registro.dname || '</Depto>');
    dbms_output.put_line('  </Seq>');
    dbms_output.put_line('</Registro>');    
  END LOOP;
END;  

qualquer coisa, manda ai.

[dr_gori]

Achei uma série de informações sobre XML no Oracle
http://www.orafaq.com/faqxml.htm
http://www.oracle.com/xml/

Talvez ajude

Selecionar tudo

How does one map relational data from tables to XML?
If you're using Oracle 8i, use the DBMS_XMLQUERY and DBMS_XMLSAVE JAVA based packages. For Oracle 9i, use the C-based package DBMS_XMLGEN.

Look at the following Oracle 9i code example:

	connect scott/tiger
	set serveroutput on
	DECLARE
	        Ctx    DBMS_XMLGEN.ctxHandle;   -- Var's to convert SQL output to XML
	        xml    clob;
	        emp_no NUMBER := 7369;

	        xmlc   varchar2(4000);          -- Var's required to convert lob to varchar
	        off    integer := 1;
	        len    integer := 4000;
	BEGIN
	        Ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = '||emp_no);
	        DBMS_XMLGen.setRowsetTag(Ctx, 'EMP_TABLE');
	        DBMS_XMLGen.setRowTag(Ctx, 'EMP_ROW');
	        DBMS_XMLGEN.closeContext(Ctx);
	        xml := DBMS_XMLGEN.getXML(Ctx);

	        DBMS_LOB.READ(xml, len, off, xmlc);   -- Display first part on screen
	        DBMS_OUTPUT.PUT_LINE(xmlc);
	END;
	/

The same results can be achieved using SQLX (see http://sqlx.org/). Some of the SQLX functions are XMLElement(), XMLForest(), XMLSequence(), etc. Look at this example.

	set long 32000
	SELECT XMLELEMENT("EMP_TABLE",
	        (select XMLELEMENT("EMP_ROW",
	                   XMLFOREST(empno, ename, job, mgr, hiredate, sal, deptno)
	                )
	         from   emp
	         where  empno = 7369))
	from dual;

An older Oracle 8i example:

	connect scott/tiger
	set serveroutput on
	DECLARE
		Ctx    DBMS_XMLQuery.ctxType;	-- Var's to convert SQL output to XML
	        xml    clob;
		emp_no NUMBER := 7369;

		xmlc   varchar2(4000);		-- Var's required to convert lob to varchar
		off    integer := 1;
		len    integer := 4000;
	BEGIN
		Ctx := DBMS_XMLQuery.newContext('SELECT * FROM emp WHERE empno = :empno');
		DBMS_XMLQuery.setBindValue(Ctx, 'empno', emp_no);
		xml := DBMS_XMLQuery.getXML(Ctx);
		DBMS_XMLQuery.closeContext(Ctx);

		DBMS_LOB.READ(xml, len, off, xmlc);   -- Display first part on screen
		DBMS_OUTPUT.PUT_LINE(xmlc);
	END;
	/

# Back to top of file
How does one store and extract XML data from Oracle?
XML data can be stored in Oracle (9.2.0 and above) using the XMLType data type. Look at this example:

	connect scott/tiger

	create table XMLTable (doc_id number, xml_data XMLType);

	insert into XMLTable values (1,
	        XMLType('<FAQ-LIST>
	           <QUESTION>
	                <QUERY>Question 1</QUERY>
	                <RESPONSE>Answer goes here.</RESPONSE>
	           </QUESTION>
	        </FAQ-LIST>'));

	select extractValue(xml_data, '/FAQ-LIST/QUESTION/RESPONSE')  -- XPath expression
	from   XMLTable
	where  existsNode(xml_data, '/FAQ-LIST/QUESTION[QUERY="Question 1"]') = 1;

# Back to top of file
Can one index XML data loaded into the database?
Yes, look at this example:

	create index XMLTable_ind on XMLTable
	        (extractValue(xml_data, '/FAQ-LIST/QUESTION/QUERY') );