Problem solve
Get help with specific problems with your technologies, process and projects.
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?
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, e.g.
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?
That's a great question. If I understand correctly, what you are trying to do is combine two XML fragments into a single XML document.
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.
