Select xmlelement("FIRST STRING", xmlforest(cl1 "COL1", col2 "COL2")) into xmlstring1 from table1 where col1 = test;
Then a number of similar statements to get a number of XML strings and a final statement
select xmlelement("FINAL STRING", xmlforest (col5 "COL5", col6 "COL6"||xmlstring1||col7 "COL7"||xmlstring2)) into finalstring where . . .
Can concat be used within XMLElement or XMLForest in this way?
I won't try to guess why you're doing this in separate SELECT statements, or why you would want to recombine using SQL instead of PL/SQL. But, here is a working example to do what I think you are trying to do.
DECLARE XMLSTRING1 xmlType; XMLSTRING2 xmlType; XMLSTRING3 xmlType; BEGIN SELECT xmlElement("FIRST_STRING", xmlForest(ename "COL1", job "COL2")) INTO xmlString1 FROM emp WHERE ROWNUM = 1; DBMS_OUTPUT.put_line( xmlString1.getStringVal() ); SELECT xmlElement("SECOND_STRING", xmlForest(sal "COL1", mgr "COL2")) INTO xmlString2 FROM emp WHERE ROWNUM = 1; DBMS_OUTPUT.put_line( xmlString2.getStringVal() ); SELECT xmlElement( "THIRD_STRING", xmlString1, xmlString2 ) INTO xmlString3 FROM DUAL; DBMS_OUTPUT.put_line( xmlString3.getStringVal() ); END;
The first and second SELECT staements gather two XML fragments and the third statement uses XMLElement to combine them. I used DBMS_OUTPUT to display the intermediate results.
Hope that helps.
This was first published in August 2005