By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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 a step-by-step procedure for importing text to an Oracle database using a stored procedure.continue reading
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.