3 Replies Latest reply on Feb 23, 2011 12:46 PM by odie_63

    Specifying empty tag style

      Hi all. I'm using dbms_xmlquery.useNullAttributeIndicator, and for a reason I'd rather not go into I need the tags to be separate open-close tags (<tag></tag>) instead of the more elegant <tag/> tag. I'd also like to suppress the NULL="TRUE" attribute if possible. I'm open to changing our routine to use DBMS_XMLGEN if it helps in the above. Many thanks in advance for your help.
        • 1. Re: Specifying empty tag style

          With DBMS_XMLGEN, you can get rid of the NULL attribute, but empty tags will appear in the short form :
          SQL> set serveroutput on
          SQL> DECLARE
            2    ctx    dbms_xmlgen.ctxHandle;
            3    sqlstr varchar2(4000) := 'SELECT * FROM scott.emp WHERE rownum = 1';
            4    res    clob;
            5  BEGIN
            6    ctx := dbms_xmlgen.newContext(sqlstr);
            7    dbms_xmlgen.setNullHandling(ctx, dbms_xmlgen.EMPTY_TAG);
            8    res := dbms_xmlgen.getXML(ctx);
            9    dbms_xmlgen.closeContext(ctx);
           10    dbms_output.put_line(res);
           11  END;
           12  /
          <?xml version="1.0"?>
          PL/SQL procedure successfully completed
          On the latest release (11.2), and only for particular cases, you might be able to do what you want with XQuery.
          See § Oracle XQuery Extension-Expression Pragmas on this page :

          Here's also an example :
          SELECT XMLSerialize(
            '(#ora:view_on_null empty #)
              for $i in fn:collection("oradb:/SCOTT/EMP")/ROW
              where $i/EMPNO = 7566
              return $i
            returning content
           as clob
          FROM dual;
          • 2. Re: Specifying empty tag style

            And how do you set null handling if you're using a select statement (with xmlelement, xmlagg, xmlforest, and the like) to generate the xml?

            • 3. Re: Specifying empty tag style

              There's no explicit option for NULL handling in SQL/XML functions.

              Only XMLElement returns an empty element if the argument is NULL.