Simple example of how to generate an XML file on Oracle

Asked

Viewed 4,076 times

7

One would have a simple example of a PLSQL Procedure (or documentation location) to generate an XML file based on a Schema (xsd), the data would be generated from an SQL query. In reality archives of RPS of ANS (Agência Nacional de Saúde BR).

sql xml oracle pl-sql xsd
  • 1

    See if it helps you this reply.

2 answers

1

There are some ways to generate a XML in the PL-SQL. But in the case of XSD, I imagine you should do a validation of Schema. So I’m going to divide the answer into two parts, first into generation examples of 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 specified 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, ie XML is generated gross and any abnormality will not be validated and consequently a XML invalid, a more refined solution would be to parse the CLOB to prevent any failure in the XML would compromise a later process. It will be seen in item 4.

2. XMLGEN package

Another very simple way to generate a document XML is the use of the package XMLGEN. The function getXml() takes the parameters for the query SQL and the type of metadata (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;

Upshot:

<?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 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 data transformation by mapping any query to the XML and vice versa. The XSU provides basic functionality to obtain and place data from and to a database.

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

The generation of XML calling the function getXML() results in a CLOB containing the document XML. A necessary context identifier 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 scheme definition or XML explicitly defined tag names different from 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 the printing of a CLOB on screen. Finally, the query identifier must 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. XMLDOM package

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

In the declaration section, you need a set of DOM references. The identifier DOMDocument is the most important. It will be used in most subsequent calls. In addition, you need different DOM node identifiers to reference the main 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 main node. The returned identifier is used as a root node for subsequent calls.

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 line 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 a 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)
  );

Now text elements can be added to the DOM document. In the first step, a new element called EMP_NO is created. This element is attached as child node to the user node. In the second step, a text node is created that contains the record data, in this case the employee number. This text node is attached as 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 text elements NAME and DEPT_NO.

After all records have been processed and all data has been uploaded to 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 can save the file XML in all locations accessible by the oracle user of the OS, regardless of the current boot parameter UTL_FILE_DIR. 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, first you need to register the XSD.

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

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

And the verification is in accordance with 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 him 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 check whether the instances XMLType are compliant using isSchemaValid. If it is in compliance, 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 isn’t compliant by calling XMLType.schemaValidate. To use this, your XML must have the correct space to name information.

To do this, add:

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

Do this and now you will receive an exception if the XML is not in conformity:

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 precedent to validate the XML Schema XSD:

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 the 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

0

  • Friend ,if I had the code I would have no topic, I asked for an example or the documentation.

  • @Edvaldo Costa , grateful , but as he did to apply xml to schema ?

Browser other questions tagged sql xml oracle pl-sql xsd

You are not signed in. Login or sign up in order to post.