2 Replies Latest reply: Dec 6, 2012 4:49 AM by user8954231 RSS

    Add xmlns tag to Xml file using DBMS_XMLGEN

    user8954231
      I am using Oracle Release 10.1.0.4.2 version .

      I want to create a xml file according to some data available in the relational database tables. For the same i created object types and then view with the object types since data is scattered in many tables.

      Then i used DBMS_XMLGEN.getxml to generate the xml with query from the object view.

      Is there an option to add xmlns attribute to the generated xml file.

      Please let me know if any additional details are needed.
        • 1. Re: Add xmlns tag to Xml file using DBMS_XMLGEN
          odie_63
          If you want to add the namespace declaration in the root element, I don't think it's possible with DBMS_XMLGEN directly.
          You can instruct DBMS_XMLGEN to treat some object fields as XML attributes by prefixing them with "@" (e.g. "@my_attribute") but you cannot control that for enclosing elements.

          A possible solution is to add it afterwards using XSLT, or insertChildXML, but apparently the latter is not available in your version.

          However...
          For the same i created object types and then view with the object types since data is scattered in many tables.
          Since the data comes from different tables, why not use SQL and SQL/XML functions to build the document?
          It's straightforward, simple, and doesn't need additional objects.
          select xmlelement("Departments",
                   xmlattributes('http://xmlns.example.org' as "xmlns")
                 , xmlagg(
                     xmlelement("Department",
                       xmlattributes(d.deptno as "Id")
                     , xmlelement("Name", d.dname)
                     , xmlelement("Employees"
                       , xmlagg(
                           xmlelement("Employee",
                             xmlattributes(e.empno as "Id")
                           , xmlforest(
                               e.ename as "Name"
                             , e.job as "Job"
                             )
                           ) order by e.empno
                         )
                       )
                     ) order by d.deptno
                   )
                 )
          from scott.dept d
               join scott.emp e on e.deptno = d.deptno
          group by d.deptno, d.dname
          ;
          Output :
          <Departments xmlns="http://xmlns.example.org">
            <Department Id="10">
              <Name>ACCOUNTING</Name>
              <Employees>
                <Employee Id="7782">
                  <Name>CLARK</Name>
                  <Job>MANAGER</Job>
                </Employee>
                <Employee Id="7839">
                  <Name>KING</Name>
                  <Job>PRESIDENT</Job>
                </Employee>
                <Employee Id="7934">
                  <Name>MILLER</Name>
                  <Job>CLERK</Job>
                </Employee>
              </Employees>
            </Department>
            <Department Id="20">
              <Name>RESEARCH</Name>
              <Employees>
                <Employee Id="7369">
                  <Name>SMITH</Name>
                  <Job>CLERK</Job>
                </Employee>
                <Employee Id="7566">
                  <Name>JONES</Name>
                  <Job>MANAGER</Job>
                </Employee>
                <Employee Id="7902">
                  <Name>FORD</Name>
                  <Job>ANALYST</Job>
                </Employee>
              </Employees>
            </Department>
            <Department Id="30">
              <Name>SALES</Name>
              <Employees>
                <Employee Id="7499">
                  <Name>ALLEN</Name>
                  <Job>SALESMAN</Job>
                </Employee>
                <Employee Id="7521">
                  <Name>WARD</Name>
                  <Job>SALESMAN</Job>
                </Employee>
                <Employee Id="7654">
                  <Name>MARTIN</Name>
                  <Job>SALESMAN</Job>
                </Employee>
                <Employee Id="7698">
                  <Name>BLAKE</Name>
                  <Job>MANAGER</Job>
                </Employee>
                <Employee Id="7844">
                  <Name>TURNER</Name>
                  <Job>SALESMAN</Job>
                </Employee>
                <Employee Id="7900">
                  <Name>JAMES</Name>
                  <Job>CLERK</Job>
                </Employee>
              </Employees>
            </Department>
          </Departments>
          • 2. Re: Add xmlns tag to Xml file using DBMS_XMLGEN
            user8954231
            Thanks for the response. I will go for the option you suggested. It seems to be flexible too.