Concatenating XML fragments
Using XMLElement and XMLForest I have created several XML "strings." In a final XMLElement select statement with XMLForest I want to concat the earlier strings within the output of the final statement. Can concat be used within XMLElement or XMLForest in this way?
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments