XML, které obsahuje vícenásobné uzly (tzv. multiple nodes) je nutné v Oracle databázi zpracovat funkcí XMLSequence. Ukážeme si její použití ve třech verzích. Nejprve s XML bez namespace, potom s nadefinovaným namespace a nakonec použití přímo v SQL výrazu. Co je to XML, případně namespace se dozvíte např. na W3Schools.

[English version of the article – How-To processing XML with multiple nodes (using XMLSequence in Oracle)]

Ukázka použití bez nadefinovaného namespace

Předpokladem je následující dokument, kde „multiple-node“ je tag ipAddress – vyskytuje se pod s sebou v jedné úrovni vícekrát. Tedy jedná se o pole IP adres s položkami value a 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>

V další ukázce je použit jednoduchý datový typ RECORD, do kterého budeme ukládat hodnoty value a mask pomocí zápisu do pole využitím klauzule BULK COLLECT.

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

A tady je zmíněný skript, který demonstruje použití 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;
/

Výstupem skriptu je následující seznam IP adres:

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

Finta je v tomto případě v použití FROM TABLE(XMLSEQUENCE(…)). Funkce XMLSEQUENCE totiž vrací VARRAY a nad ním je již možné použít příkaz TABLE(…).

Ukázka použití s nadefinovaným namespace

V tomto případě nadefinujeme například namespace „ip„:

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

Upravíme testovací XML:

<?xml version="1.0" encoding="UTF-8"?>
<ip:ipAddressList xmlns:ip="http:/martin-mares.cz/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>

A výsledný upravený skript, který umí pracovat s 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;
/

Výstupem je opět seznam IP adres:

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

Ve výše zmíněném skriptu prosím věnujte pozornost místu „EXTRACTVALUE“ (místo value je nyní použito ip:value)! Je velmi důležité při zpracování funkcí EXTRACT nezapomenout na prefix ip:!, jinak příklad nebude fungovat. V ostatních případech může dojít k vyjímce

ORA-31013: Neplatný výraz XPATH

Ukázka použití v SQL výrazu

Nyní pouze spojíme dohromady to co jsme už použily v předchozích příkladech:

    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;

Výsledkem je seznam IP adres:

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

UPDATE 19.10.2010: Přidání kapitoly demonstrující aktualizaci jednotlivých uzlů.

Aktualizace uzlů pomocí SQL funkce UpdateXML

Tento příklad ukazuje, jak změnit data v XMLType před tím, než je zpracujeme funkcí XMLSequence. U uzlů, které obsahují masku 255.255.255.0 změní hodnotu na 255.255.255.128 pomocí SQL funkce UpdateXML a pomocí správného výrazu XPath:

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;
/

Výsledek:

[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

1 Comments

Leave a comment

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *