XML, which contains multiple nodes is necessary in the Oracle database parse with function XMLSequence. I will show you the application in three versions. First, with no XML namespace, then with the defined XML namespace and eventually use directly in the SQL expression. What is XML, or XML namespace, see W3Schools.

[Česká verze článku – Jak na zpracování XML s vícenásobnými uzly (XMLSequence v Oracle)]

Example without XML namespace

The assumption is the following document, where “multiple-node” is a tag ipAddress – occurs with the other in a level several times. Therefore, it is an array of IP addresses with items value and mask

<?xml version="1.0" encoding="UTF-8"?>
<ipAddressList>
	<ipAddress>
		<value>10.10.0.1</value>
		<mask>255.0.0.0</mask>
	</ipAddress>
	<ipAddress>
		<value>192.168.1.1</value>
		<mask>255.255.255.0</mask>
	</ipAddress>
</ipAddressList>

The next demo is a simple data type RECORD, in which we store values value and mask with the entry in the field using the BULK COLLECT clause.

    TYPE rec_ip_address IS RECORD (
        ip_address_value VARCHAR2(15),
        ip_address_mask  VARCHAR2(15)
    );

And there is mentioned a script that demonstrates the use XMLSequence.

DECLARE
    l_xml_source      XMLType;

    TYPE rec_ip_address IS RECORD (
        ip_address_value VARCHAR2(15),
        ip_address_mask  VARCHAR2(15)
    );

    TYPE type_ip_address IS TABLE OF rec_ip_address INDEX BY BINARY_INTEGER;
    l_ip_address_list type_ip_address;

BEGIN
    l_xml_source := XMLType('<ipAddressList>
<ipAddress>
  <value>10.10.0.1</value>
  <mask>255.0.0.0</mask>
</ipAddress>
<ipAddress>
  <value>192.168.1.1</value>
  <mask>255.255.255.0</mask>
</ipAddress>
</ipAddressList>');

    SELECT EXTRACTVALUE(VALUE(xml_list), '//value') AS ip_address_from
          ,EXTRACTVALUE(VALUE(xml_list), '//mask') AS ip_address_to
      BULK COLLECT
      INTO l_ip_address_list
      FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml_source, 'ipAddressList/ipAddress'))) xml_list;

    IF (l_ip_address_list.COUNT > 0) THEN

        FOR i IN l_ip_address_list.FIRST..l_ip_address_list.LAST LOOP
            DBMS_OUTPUT.put_line('[' || i || '] = ' || l_ip_address_list(i).ip_address_value || '/' || l_ip_address_list(i).ip_address_mask);
        END LOOP;

    END IF;

END;
/

The output of the script is the following list of IP addresses:

SQL> DBMS Output (Session: [1] ADMIN@TEST_DB.WORLD at: 25.08.2010 23:30:33):
SQL> ----------------------------------------------------------------------
SQL> [1] = 10.10.0.1/255.0.0.0
SQL> [2] = 192.168.1.1/255.255.255.0

Trick in this case is the use of the FROM TABLE (XMLSEQUENCE (…)). XMLSEQUENCE function returns the VARRAY, and above it is already possible to use command TABLE(…).

Example of use with defined XML namespace

In case define namespace such example:

xmlns:ip="http:/martin-mares.com/sampleIpAddressList.xsd"

Modify the test XML like this:

<?xml version="1.0" encoding="UTF-8"?>
<ip:ipAddressList xmlns:ip="http:/martin-mares.com/sampleIpAddressList.xsd">
	<ip:ipAddress>
		<ip:value>10.10.0.1</ip:value>
		<ip:mask>255.0.0.0</ip:mask>
	</ip:ipAddress>
	<ip:ipAddress>
		<ip:value>192.168.1.1</ip:value>
		<ip:mask>255.255.255.0</ip:mask>
	</ip:ipAddress>
</ip:ipAddressList>

And we have A final revised script that can work with the namespace:

DECLARE
    l_xml_source      XMLType;
    l_namespace       VARCHAR2(2000) := 'xmlns:ip="http:/martin-mares.cz/sampleIpAddressList.xsd"';

    TYPE rec_ip_address IS RECORD (
        ip_address_value VARCHAR2(15),
        ip_address_mask  VARCHAR2(15)
    );

    TYPE type_ip_address IS TABLE OF rec_ip_address INDEX BY BINARY_INTEGER;
    l_ip_address_list type_ip_address;

BEGIN
    l_xml_source := XMLType('<ip:ipAddressList ' || l_namespace || '>
<ip:ipAddress>
  <ip:value>10.10.0.1</ip:value>
  <ip:mask>255.0.0.0</ip:mask>
</ip:ipAddress>
<ip:ipAddress>
  <ip:value>192.168.1.1</ip:value>
  <ip:mask>255.255.255.0</ip:mask>
</ip:ipAddress>
</ip:ipAddressList>');

    SELECT EXTRACTVALUE(VALUE(xml_list), '//ip:value', l_namespace) AS ip_address_from
          ,EXTRACTVALUE(VALUE(xml_list), '//ip:mask', l_namespace) AS ip_address_to
      BULK COLLECT
      INTO l_ip_address_list
      FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml_source, 'ip:ipAddressList/ip:ipAddress', l_namespace))) xml_list;

    IF (l_ip_address_list.COUNT > 0) THEN

        FOR i IN l_ip_address_list.FIRST..l_ip_address_list.LAST LOOP
            DBMS_OUTPUT.put_line('[' || i || '] = ' || l_ip_address_list(i).ip_address_value || '/' || l_ip_address_list(i).ip_address_mask);
        END LOOP;

    END IF;

END;
/

The output is again a list of IP addresses:

SQL> DBMS Output (Session: [1] ADMIN@ZIS_VT.WORLD at: 25.08.2010 23:48:34):
SQL> ----------------------------------------------------------------------
SQL> [1] = 10.10.0.1/255.0.0.0
SQL> [2] = 192.168.1.1/255.255.255.0

In the above script, please pay attention to EXTRACTVALUE. It’s very important to use ip:value instead of value. Otherwise Oracle can return exception:

RA-31013: Invalid XPATH expression

Example with clean SQL expression

Now only you combine what we’ve used in previous examples:

    SELECT EXTRACTVALUE(VALUE(xml_list), '//value') AS ip_address
          ,EXTRACTVALUE(VALUE(xml_list), '//mask') AS mask
      FROM TABLE(XMLSEQUENCE(EXTRACT(XMLType('<ipAddressList>
                                                <ipAddress>
                                                  <value>10.10.0.1</value>
                                                  <mask>255.0.0.0</mask>
                                                </ipAddress>
                                                <ipAddress>
                                                  <value>192.168.1.1</value>
                                                  <mask>255.255.255.0</mask>
                                                </ipAddress>
                                                </ipAddressList>'), 'ipAddressList/ipAddress'))) xml_list;

The result is a list of IP addresses:

SQL> IP_ADDRESS      MASK
SQL> --------------- ---------------
SQL> 10.10.0.1       255.0.0.0
SQL> 192.168.1.1     255.255.255.0

UPDATE 10/19/2010: Add next chapter demostrate how to Update XML nodes.

How to XML node update

This example shows how to change the data in XMLType before the process XMLSequence function. For nodes that contain a mask value of 255.255.255.0 changes to 255.255.255.128 using SQL function UpdateXML and the correct XPath expression:

DECLARE
    l_xml_source      XMLType;
    l_namespace       VARCHAR2(2000) := 'xmlns:ip="http:/martin-mares.cz/sampleIpAddressList.xsd"';
    l_default_mask    VARCHAR2(2000) := '255.255.255.0';
    l_new_mask        VARCHAR2(2000) := '255.255.255.128';

    TYPE rec_ip_address IS RECORD (
        ip_address_value VARCHAR2(15),
        ip_address_mask  VARCHAR2(15)
    );

    TYPE type_ip_address IS TABLE OF rec_ip_address INDEX BY BINARY_INTEGER;
    l_ip_address_list type_ip_address;

BEGIN
    l_xml_source := XMLType('<ip:ipAddressList ' || l_namespace || '>
<ip:ipAddress>
  <ip:value>10.10.0.1</ip:value>
  <ip:mask>255.0.0.0</ip:mask>
</ip:ipAddress>
<ip:ipAddress>
  <ip:value>192.168.1.1</ip:value>
  <ip:mask>' || l_default_mask || '</ip:mask>
</ip:ipAddress>
<ip:ipAddress>
  <ip:value>192.168.1.2</ip:value>
  <ip:mask>' || l_default_mask || '</ip:mask>
</ip:ipAddress>
<ip:ipAddress>
  <ip:value>192.168.1.3</ip:value>
  <ip:mask>' || l_default_mask || '</ip:mask>
</ip:ipAddress>
</ip:ipAddressList>');

    SELECT UPDATEXML(l_xml_source, '/ip:ipAddressList/ip:ipAddress[ip:mask = ''' || l_default_mask || ''']/ip:mask/text()', l_new_mask, l_namespace)
    INTO l_xml_source
    FROM dual;

    SELECT EXTRACTVALUE(VALUE(xml_list), '//ip:value', l_namespace) AS ip_address_from
          ,EXTRACTVALUE(VALUE(xml_list), '//ip:mask', l_namespace) AS ip_address_to
      BULK COLLECT
      INTO l_ip_address_list
      FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml_source, 'ip:ipAddressList/ip:ipAddress', l_namespace))) xml_list;

    IF (l_ip_address_list.COUNT > 0) THEN

        FOR i IN l_ip_address_list.FIRST..l_ip_address_list.LAST LOOP
            DBMS_OUTPUT.put_line('[' || i || '] = ' || l_ip_address_list(i).ip_address_value || '/' || l_ip_address_list(i).ip_address_mask);
        END LOOP;

    END IF;

END;
/

The modified result is:

[1] = 10.10.0.1/255.0.0.0
[2] = 192.168.1.1/255.255.255.128
[3] = 192.168.1.2/255.255.255.128
[4] = 192.168.1.3/255.255.255.128

Post to Twitter

7 Comments

  1. Pingback: Jak na zpracování XML s vícenásobnými uzly (XMLSequence v Oracle) | Martin Mareš (blog)

    • Martin Mareš

      Reply

      Please read updated article. Chapter How to XML node update

  2. Fabio Justino

    Reply

    Hi!

    I have the xml above:

    xxxx.

    yyyy.

    Nota Fiscal de exemplo NFeletronica.com

    I used the query above to select the “cProd” node but this not work.

    Select extractValue (value(nnf),’/NFe/infNFe/ide/det/@nItem’, ‘xmlns=”http://www.portalfiscal.inf.br/nfe”‘) prod
    from xmltbl,
    TABle(XMLSequence(extract(xmlcolumn,’/NFe/infNFe/ide/det’,’xmlns=”http://www.portalfiscal.inf.br/nfe”‘))) NNF

    This query return null.
    Whats wrong?
    Thanks!

    • Fabio Justino

      Reply

      1-
      2 –
      4 –
      5 –
      6 –
      7 –
      8 – xxxx.
      9 –
      10 –
      11 –
      12 –
      13 – yyyy.
      14 –
      15 –
      16 –
      17 -Nota Fiscal de exemplo NFeletronica.com
      18 –
      19 –

        • Martin Mareš

          Reply

          Sorry for this. But this is WordPress “function”. You must paste XML tag with &lt; and &gt; instead of < and >

Leave a comment

Your email address will not be published. Required fields are marked *