CODEWITHSUNDEEP
xmloracle-databaseplsql
Tom
Tom

Reputation: 78

Is there a easy way to suppress the XML row tags of a collection in Oracle?

I have a query that I am generating the XML from in Oracle using the DBMS_XMLGEN package.

As an example, I am using a cursor as follows:

SELECT
   A.NAME primaryName,
   (CURSOR(SELECT B.NAME AS NAME FROM B WHERE B.ID=A.ID)) SYNONYMS
FROM
  A

I have a query context, qtx, and set the query to the above for that context. Calling:

result := DBMS_XMLGEN.getXML(qryCtx);

gets me almost where I want to be, from the viewpoint of the generated XML:

<PRIMARYNAME>Main Name</PRIMARYNAME>
  <SYNONYMS>
   <SYNONYMS_ROW>
    <NAME>Synonym1</NAME>
   </SYNONYMS_ROW>
   <SYNONYMS_ROW>
    <NAME>Synonym2</NAME>
   </SYNONYMS_ROW>
  </SYNONYMS>

What I would really like to do is suppress the SYNONYMS_ROW tag. I've also tried a (CAST(MULTISET( <query>) and have similar results.

I know I could do a CLOB search and replace, but it seem like there should be a slightly easier or better engineered approach (ie, should I define the desired xsd and use that somehow?). I could also do a full stored procedure and build the tags as needed on the fly using cursors, but it would be nice to have a single SQL statement to use instead. Thanks for any suggestions



Thanks Nick - it turned out that the easiest way to solve the issue I describe was to use the XMLAGG function and generate my XML result slightly differently.

select
   XMLELEMENT("primaryName",A.Name),
   xmlelement("synonyms",
       (SELECT XMLAGG(XMLELEMENT("name",b.name) ) from b
        where b.id=a.id and b.type='S') )
from
   A

Upvotes: 0

Views: 4631

Answers (2)

Tom
Tom

Reputation: 78

See the comments I added at the end of the question for how I ultimately decided to go.

Upvotes: 0

Nick
Nick

Reputation: 2514

You'll need to drop down to PL/SQL where you have more control over the dbms_xmlgen package. For instance, here is how you can change the behavior of the row tags it sets.

declare
qryctx DBMS_XMLGEN.ctxhandle;
results xmltype;
begin

  qryctx := dbms_xmlgen.newcontext('select foo from bar where id = my_id');
  dbms_xmlgen.SETBINDVALUE(qryctx,'my_id',id);
    dbms_xmlgen.setRowTag(qryCtx,NULL);
    dbms_xmlgen.setRowSetTag(qryCtx,NULL);
  results := dbms_xmlgen.getxmltype(qryctx);
  dbms_xmlgen.closecontext(qryctx);
  return results;
end;

This package will allow you not only to change the row tag, but also the tag that goes around all the rows. In the above example, I suppressed the row tag by passing NULL as the second arguement into the dbms_xmlgen.setRowTag call. I hope this helps.

Upvotes: 1

Related Questions