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.
Dig Deeper on Oracle and SQL
Related Q&A from Lewis Cunningham
I need to somehow log the execution time for two procedures every time they are called and save that data (in a table?). Do you know of a way to ... Continue Reading
How can I tell if a column is a NUMBER or VARCHAR in PL/SQL? Is there a function to do this? Continue Reading
When you concatenate, Oracle treats a clob as a varchar2 and then, at over 4000 characters, the string becomes too long to handle. Do you have any ... Continue Reading