Q
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?

Lewis Cunningham
By
Published: 15 Aug 2005
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.

Dig Deeper on Oracle and SQL

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close