3 Replies Latest reply: May 6, 2014 9:02 AM by KKallaur RSS

    How to add extra note between <ROWSET> and <ROW>

    user8461308

      Hello All,

       

      Is there a way to add extra NODE between <ROWSET> and <ROW> that appears ONCE per each XML document?

       

      <?xml version="1.0"?>

      <ROWSET>

      <ROW>

        <EMPNO>7369</EMPNO>

        <ENAME>SMITH</ENAME>

        <JOB>CLERK</JOB>

        <MGR>7902</MGR>

        <HIREDATE>17-DEC-80</HIREDATE>

        <SAL>800</SAL>

        <DEPTNO>20</DEPTNO>

      </ROW>

      <ROW>

        <EMPNO>7499</EMPNO>

        <ENAME>ALLEN</ENAME>

        <JOB>SALESMAN</JOB>

        <MGR>7698</MGR>

        <HIREDATE>20-FEB-81</HIREDATE>

        <SAL>1600</SAL>

        <COMM>300</COMM>

        <DEPTNO>30</DEPTNO>

      </ROW>

      ...

      </ROWSET>

       

       

      would be

       

      <?xml version="1.0"?>

      <ROWSET>

      <test>new</test>

      <ROW>

        <EMPNO>7369</EMPNO>

        <ENAME>SMITH</ENAME>

        <JOB>CLERK</JOB>

        <MGR>7902</MGR>

        <HIREDATE>17-DEC-80</HIREDATE>

        <SAL>800</SAL>

        <DEPTNO>20</DEPTNO>

      </ROW>

      <ROW>

        <EMPNO>7499</EMPNO>

        <ENAME>ALLEN</ENAME>

        <JOB>SALESMAN</JOB>

        <MGR>7698</MGR>

        <HIREDATE>20-FEB-81</HIREDATE>

        <SAL>1600</SAL>

        <COMM>300</COMM>

        <DEPTNO>30</DEPTNO>

      </ROW>

      ...

      </ROWSET>

       

      Thank you in advance,  Sonya

        • 1. Re: How to add extra note between <ROWSET> and <ROW>
          KKallaur

          How are you retrieving the data? give us an example of how you are retrieving your rowset. If you're using DBMS_XMLGEN, you can rename the rowset tag.

          If you need that extra node, I would recommend using SQL/XML to generate your xml, which will give you a lot more flexibility in data retrieval.

          • 2. Re: How to add extra note between <ROWSET> and <ROW>
            user8461308

            I am using dbms_xmlgen.getxml and setting rowsettag to <MAIN> and rowtag to <order_entry>

            I need an additional NODE that appears once before rowtag NODE stating <TEST> .

            • 3. Re: How to add extra note between <ROWSET> and <ROW>
              KKallaur

              I don't think that's possible with dbms_xmlgen. I would suggest exploring sql/xml to generate your own xml using the xmlelement, xmlagg, etc keywords.

              For example:

              select xmlelement("ROWSET"

                             , xmlelement("TEST", 'NEW')

                             , XMLAGG(XMLELEMENT("ROW"

                                , XMLFOREST(EMPNO

                                         , ENAME

                                         , JOB

                                         , MGR

                                         , HIREDATE

                                         , SAL

                                         , COMM

                                         , DEPTNO

                                         )AS FOREST --XMLFOREST

                                ) --ROW

                                 ) --AGG

                             ) --ROWSET

              from EMP;

               

              will give you something like the following result:

              <?xml version="1.0"?>

              <ROWSET>

                <TEST>NEW</TEST>

                <ROW>

                  <EMPNO>7369</EMPNO>

                  <ENAME>SMITH</ENAME>

                  <JOB>CLERK</JOB>

                  <MGR>7902</MGR>

                  <HIREDATE>1980-12-17</HIREDATE>

                  <SAL>800</SAL>

                  <DEPTNO>20</DEPTNO>

                </ROW>

                <ROW>

                  <EMPNO>7499</EMPNO>

                  <ENAME>ALLEN</ENAME>

                  <JOB>SALESMAN</JOB>

                  <MGR>7698</MGR>

                  <HIREDATE>1980-02-20</HIREDATE>

                  <SAL>1600</SAL>

                  <COMM>300</COMM>

                  <DEPTNO>30</DEPTNO>

                </ROW>

              </ROWSET>