3 Replies Latest reply: Feb 23, 2011 6:46 AM by odie_63 RSS

    Specifying empty tag style

    pnosko
      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
          odie_63
          Hi,

          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"?>
          <ROWSET>
           <ROW>
            <EMPNO>7369</EMPNO>
            <ENAME>SMITH</ENAME>
            <JOB>CLERK</JOB>
            <MGR>7902</MGR>
            <HIREDATE>17/12/80</HIREDATE>
            <SAL>800</SAL>
            <COMM/>
            <DEPTNO>20</DEPTNO>
           </ROW>
          </ROWSET>
          
           
          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 :
          http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb_xquery.htm

          Here's also an example :
          SELECT XMLSerialize(
           document
           XMLQuery(
            '(#ora:view_on_null empty #)
            {
             <ROWSET>
             {
              for $i in fn:collection("oradb:/SCOTT/EMP")/ROW
              where $i/EMPNO = 7566
              return $i
             }
             </ROWSET>
            }'
            returning content
           )
           as clob
          )
          FROM dual;
          
          <ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981-04-02</HIREDATE><SAL>2975</SAL><COMM></COMM><DEPTNO>20</DEPTNO></ROW></ROWSET>
          • 2. Re: Specifying empty tag style
            Lmocsi
            Hi,

            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?

            Regards,
            Lmocsi
            • 3. Re: Specifying empty tag style
              odie_63
              Hi,

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

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