Q

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.

This was first published in August 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close