Simple example of how to generate an XML file in Oracle

There are some ways to generate a XML in PL-SQL. But in the case of XSD, I imagine that you should do a validation of Schema. So I will divide the answer into two parts, first in examples of generating XML and finally the validation of Schema.

Options to generate the XML:

1. DBMS_XSLPROCESSOR

This example uses CLOB to store the stream that will be the file XML and then it is created and saved in the indicated folder using the package DBMS_XSLPROCESSOR.

DECLARE
 varClob     CLOB;
 varString   VARCHAR2(4000);

BEGIN
 -- Abre a instancia do CLOB e o coloca em modo de escrita
 dbms_lob.createtemporary(varClob, TRUE);
 dbms_lob.open(varClob, dbms_lob.lob_readwrite);

 -- Variável string para armazenar parte do XML
 varString := '<?xml version="1.0" encoding="utf-8"?>' ||
              '<root>' ||
              '<teste>Este é um teste</teste>' ||
              '</root>';

 -- Cria um XML simples de testes, pode ser dado quantos APPEND quiser e
 -- ir montando o XML durante sua rotina PL/SQL
 dbms_lob.writeappend(varClob, LENGTH(varString), varString);

 -- Aqui irá de fato gerar o arquivo físico do XML
 DBMS_XSLPROCESSOR.CLOB2FILE(varClob, '/minha/pasta/', 'teste.xml', NLS_CHARSET_ID('UTF8'));

 -- Liberar dados do CLOB da memória
 dbms_lob.close(varClob);
 dbms_lob.freetemporary(varClob);
END;

Note that in this example there is no parse, that is, the XML is generated raw and any abnormality will not be validated and consequently an invalid XML, a more refined solution would be to perform the parse of the CLOB to prevent some failure in the XML compromising a later process. It will be seen in item 4.

2. Package xmlgen

Another very simple way to generate a XML document is to use the XMLGEN package. The function getXml() takes the parameters for the query SQL and the metaData type (such as DTD) and returns a CLOB containing the document XML.

SELECT xmlgen.getXml(
  'SELECT empno "EMP_NO"
        , ename "NAME"
        , deptno "DEPT_NO"
     FROM emp
    WHERE deptno = 10'
  , 0
  ) FROM dual;

Result:

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMP_NO>7782</EMP_NO>
      <NAME>CLARK</NAME>
      <DEPT_NO>10</DEPT_NO>
   </ROW>
   <ROW num="2">
      <EMP_NO>7839</EMP_NO>
      <NAME>KING</NAME>
      <DEPT_NO>10</DEPT_NO>
   </ROW>
   <ROW num="3">
      <EMP_NO>7934</EMP_NO>
      <NAME>MILLER</NAME>
      <DEPT_NO>10</DEPT_NO>
   </ROW>
</ROWSET>

The result is a root element called ROWSET, which contains a list of ROW elements. Each row element has the row number as an attribute, and each row element contains the EMP_NO, NAME, and DEPT_NO elements.

3. Using XML-SQL Utility (XSU)

XML-SQL Utility (XSU) provides a simple way to achieve transformation of data by mapping any query to XML and vice versa. XSU provides the basic functionality for getting and placing data to and from a database.

DBMS_XMLQUERY and DBMS_XMLSAVE are two packages that support XML generation and storage through XML. Here we will focus on the generation of XML.

Generating XML by calling the function getXML() results in a CLOB containing the document XML. A context identifier necessary in most subsequent calls is created in the first step.

DECLARE
  queryCtx dbms_xmlquery.ctxType;
  result CLOB;
BEGIN
  -- set up the query context
  queryCtx := dbms_xmlquery.newContext(
    'SELECT empno "EMP_NO"
          , ename "NAME"
          , deptno "DEPT_NO"
       FROM emp
      WHERE deptno = 5'
  );

If the DTD schema definition or XML explicitly defined tag names other than column names, you can change row and ROWSET tag names easily:

dbms_xmlquery.setRowTag(
      queryCtx
    , 'EMP'
  );
dbms_xmlquery.setRowSetTag(
      queryCtx
    , 'EMPSET'
  );

Ok, now you are ready to run the query and generate the result XML as CLOB. A simple procedure printClobOut() supports printing a CLOB on the screen. Finally, the query identifier should be closed to release the resources.

result := dbms_xmlquery.getXml(queryCtx);
printClobOut(result);
dbms_xmlquery.closeContext(queryCtx);

The result is something like:

<?xml version = '1.0'?>
<EMPSET>
   <EMP num="1">
      <EMP_NO>7782</EMP_NO>
      <NAME>CLARK</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
   <EMP num="2">
      <EMP_NO>7839</EMP_NO>
      <NAME>KING</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
   <EMP num="3">
      <EMP_NO>7934</EMP_NO>
      <NAME>MILLER</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
</EMPSET>

4. Package xmldom

The XMLDOM package implements the Document Object Model Interface (Dom) interface, as defined by the W3C XML recommendations. let's follow a simple example to discuss the use of the XMLDOM package.

In the Declaration section, you need a set of DOM references. The identifier DOMDocument is the most important. It will be used in most calls subsequent. In addition, you need different DOM node identifiers to reference the parent node, root node, user node, and item node for each element. The cursor selects the data that will be exposed.

DECLARE
  doc xmldom.DOMDocument;
  main_node xmldom.DOMNode;
  root_node xmldom.DOMNode;
  user_node xmldom.DOMNode;
  item_node xmldom.DOMNode;
  root_elmt xmldom.DOMElement;
  item_elmt xmldom.DOMElement;
  item_text xmldom.DOMText;
  CURSOR get_users(p_deptno NUMBER) IS
    SELECT empno
         , ename
         , deptno
         , rownum
      FROM emp
     WHERE deptno = p_deptno;

First, you create a new document identifier. Next, you create the main node for this document. The root element is called EMPSET and attached as a child node to the parent node. The returned identifier is used as the root node for calls subsequent.

BEGIN
  doc := xmldom.newDOMDocument;
  main_node := xmldom.makeNode(doc);
  root_elmt := xmldom.createElement(
      doc
    , 'EMPSET'
  );
  root_node := xmldom.appendChild(
      main_node
    , xmldom.makeNode(root_elmt)
  );

For each record found in the query, a new element called EMP is created. The row number is added as an attribute to the element. This element is attached as a child node to the root node. The returned identifier is used as the user node for subsequent calls.

FOR get_users_rec IN get_users(10) LOOP
  item_elmt := xmldom.createElement(
      doc
    , 'EMP'
  );
  xmldom.setAttribute(
      item_elmt
    , 'num'
    , get_users_rec.rownum
  );
  user_node := xmldom.appendChild(
      root_node
    , xmldom.makeNode(item_elmt)
  );

Text elements can now be added to the DOM document. In the first step, a new element named EMP_NO is created. This element is attached as a child node to the node user. At the second stage, a text node is created that contains the registration data, in this case the number of the employee. This text node is attached as a child node to the item node.

item_elmt := xmldom.createElement(
    doc
  , 'EMP_NO'
);
item_node := xmldom.appendChild(
    user_node
  , xmldom.makeNode(item_elmt)
);
item_text := xmldom.createTextNode(
    doc
  , get_users_rec.empno
);
item_node := xmldom.appendChild(
    item_node
  , xmldom.makeNode(item_text)
);

The same can be done with the NAME and DEPT_NO text elements.

After all records have been processed and all data has been loaded into the DOM document, they can be, for example, archived and their resources released:

  END LOOP;
  xmldom.writeToFile(
      doc
    , '/tmp/xml/docSample.xml'
  );
  xmldom.freeDocument(doc);
END;

Note that the package XMLDOM you can write the XML file to all locations accessible by the Oracle OS user, regardless of the current UTL_FILE_DIR boot parameter. The resulting file contains the following lines:

<?xml version = '1.0' encoding = 'UTF-8'?>
<EMPSET>
   <EMP num="1">
      <EMP_NO>7782</EMP_NO>
      <NAME>CLARK</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
   <EMP num="2">
      <EMP_NO>7839</EMP_NO>
      <NAME>KING</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
   <EMP num="3">
      <EMP_NO>7934</EMP_NO>
      <NAME>MILLER</NAME>
      <DEPT_NO>10</DEPT_NO>
   </EMP>
</EMPSET>

XSD validation

To validate XML against a XSD, you first need to register the XSD.

In this example, let's use the following XML:

<order>
  <orderId> 1 </orderId>
  <customerId> 123 </customerId>
</order>

And the check conforms to the following XSD:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="order">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:byte" name="orderId"/>
        <xs:element type="xs:byte" name="customerId"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

(generated using http://www.freeformatter.com/xsd-generator.html#ad-output )

You register it by calling DBMS_XMLschema.registerSchema:

begin
  dbms_xmlschema.registerSchema('order.xsd',
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="order">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:byte" name="orderId"/>
        <xs:element type="xs:byte" name="customerId"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>');
end;
/

You can then verify that the XMLType instances are compliant using isSchemaValid. If it conforms, then returns 1, otherwise returns 0:

declare
  xml xmltype;
begin
  xml := xmltype('<order>
  <orderId>1</orderId>
  <customerId>123</customerId>
</order>');

  dbms_output.put_line(xml.isSchemaValid('order.xsd'));
end;
/

But that doesn't give much information! You can get more details on why a document does not comply by calling XMLType.schemaValidate. To use this, your XML must have the correct namespace information.

To do this, add:

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="order.xsd"

Do this and you will now receive an exception if XML does not comply:

declare
  xml xmltype;
begin
  xml := xmltype('<order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="order.xsd">
  <orderId>1</orderId>
</order>');

  xmltype.schemaValidate(xml);
end;
/

ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00213: only 0 occurrences of particle "customerId", minimum is 1
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 8
31154. 00000 -  "invalid XML document" 
*Cause:    The XML document is invalid with respect to its XML Schema.
*Action:   Fix the errors identified and try again.

With this we can create a procedure to validate the XML XSD Schema:

CREATE OR REPLACE PROCEDURE VALIDADOR_XML(pr_xml IN xmltype) IS
/*
 * Procedure valida o xml de acordo com um schema xsd predefinido
**/
BEGIN
  dbms_xmlschema.registerSchema('order.xsd',
        '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
          <xs:element name="order">
            <xs:complexType>
              <xs:sequence>
                <xs:element type="xs:byte" name="orderId"/>
                <xs:element type="xs:byte" name="customerId"/>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:schema>');

  IF pr_xml.isSchemaValid('order.xsd') THEN
    dbms_output.put_line ('XML VALIDO');
  ELSE
    dbms_output.put_line ('XML INVALIDO');
    /*xmltype.schemaValidate(pr_xml);*/
  END IF;
END;

References

Creating XML Documents with PL / SQL

Creating an XML file with Oracle PL / SQL

Oracle® XML DB developer's Guide

Validate XML documents with XML schema in Oracle PL / SQL?

Transforming and validating xmltype data

Ask TOM - how to use isSchemaValid() in oracle 11gr2 to validate XML against the XSD scheme?

Validate XML in ORACLE against an XSD using DBMS_XMLSCHEMA and XMLTYPE.SCHEMAVALIDATE

I just left a project that generated an xml based on a base in Oracle. For this I used SQLX -- > https://oracle-base.com/articles/misc/sqlxml-sqlx-generating-xml-content-using-sql. the rules of xsd that I had to abide by, I did everything in hand.