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 last published in August 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close