Zabawy z Oracle SQL, PL/SQL i ODI: 2020

niedziela, 17 maja 2020

Obsługa XML - część 3

Cały czas działamy na tabeli TBL_XML - opisanej tutaj. Analizowany XML zawiera powtarzający się element pozycja, a w ramach tego elementu zagnieżdżone są pola:

nazwa_waluty
przelicznik
kod_waluty
kurs sredni

 
    dolar amerykański

1 USD 4,2396

Poznamy teraz konstrukcji, która w w elegancki sposób będzie pokazywać elementy i atrybuty z powtarzających się elementów

SELECT   EXTRACTVALUE( x.cxml, '//data_publikacji') data_pubblikacji ,
   EXTRACTVALUE( x.cxml, '//numer_tabeli') nr_tabeli ,
xt.*
FROM   TBL_XML x,
       XMLTABLE('//pozycja'
         PASSING x.cxml --nazwa_kolumny XML
         COLUMNS
           NAZWA_WALUTY     VARCHAR2(30 CHAR) PATH 'nazwa_waluty',
           KOD_WALUTY     VARCHAR2(3 CHAR) PATH '//*/kod_waluty',
           PRZELICZNIK    VARCHAR2(5 CHAR) PATH '//przelicznik',
           KURS_DZIENNY    VARCHAR2(10 CHAR) PATH 'kurs_sredni'
         ) xt;

Wynik: Zauważmy - wszystkie pola sa tekstowe !!!!

Jak powyższa konstrukcja działa?

Tworzona jest w pamięci tabela aliasowana przez xt
Każdy wiersz tabeli xt to powtarzający się element pozycja, opisujący parametry waluty
W nawiasie po słowie kluczowym XMLTABLE podaje się ścieżkę do analizowanego elementu
Po słowie kluczowym PASSING podaje się nazwę pola zawierającego XML
Po słowie kluczowym COLUMNS podaje się definicje i mapowania kolumn. Mapowanie występuje po słowie kluczowym PATH i może być, jak powyżej, wykonane na różne sposoby
Wyrażenie xt.* zwraca kolumny zdefiniowane w mapowaniu

Uwagi:

W sekcji COLUMNS najlepiej jest robić mapowania na typ znakowy lub na typ numeryczny(ale tylko dla liczb całkowitych). W powyższym przykładzie mapowanie typ NUMBER(4) zadziałałoby dla pola PRZELICZNIK. Dla liczb zmiennopozycyjnych (jak pole w powyższym przypadku KURS_DZIENNY) i dat, automatyczna konwersja jest źródłem problemów. Właściwą konwersję najlepiej przeprowadzić w głównym zapytaniu, a nie na poziomie mapowania
Jeśli zdefiniujemy typ o niewystarczającej długości, to podczas mapowania teksty zostaną obcięte
Jedli w danym elemencie nie wystąpi ścieżka z mapowania, wtedy dla odpowiadającej temu mapowaniu kolumny zwracana jest wartość NULL
W przypadku zmiany kolumny lub dodania nowych pól, modyfikacja istniejącego zapytania jest banalna

niedziela, 12 kwietnia 2020

Obsługa XML - część 2

Bazujemy na tej tabeli co w poprzednim wpisie. W tabeli z kursami jest jeden wiersz zawierający w formacie XML kursy następujących walut (w kolejności ich występowania):
THB
USD
AUD
HKD
CAD

Napiszemy kilka prostych zapytań.

Wyświetlenie kodu waluty z pierwszego węzła:

SELECT EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty' ) FROM TBL_XML;

Wyświetlenie kodu waluty z pierwszego węzła:

SELECT EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty' ) FROM TBL_XML;

Wyświetlenie kodu waluty z pierwszego węzła

SELECT EXTRACTVALUE(CXML,'//pozycja[ last() ]/kod_waluty' ) FROM TBL_XML;
lub
SELECT EXTRACTVALUE(CXML,'//pozycja[ position() = last() ]/kod_waluty' ) FROM TBL_XML;
Uwaga: wielkość liter ścieżce XPath ma znaczenie. Następująca ścieżka ,'//pozycja[ Position() = last() ]/kod_waluty' jest już niepoprawna

Wyświetlenie kodu waluty z przedostatniego węzła:

SELECT EXTRACTVALUE(CXML,'//pozycja[ last() -1 ]/kod_waluty' ) FROM TBL_XML;

Wyświetlenie wszystkich kodów walut (przy założeniu, że procesujemy jeden wiersz) - czyli warto dodać odwołanie do klucza w tabeli. W klauzuli CONNECT BY LEVEL należy dodać sensowne górne oszacowanie liczby węzłów

SELECT EXTRACTVALUE(CXML,'//pozycja[' || LEVEL || ']/kod_waluty' ) FROM TBL_XML
    WHERE EXTRACTVALUE(CXML,'//pozycja[' || LEVEL || ']/kod_waluty' ) IS NOT NULL AND CID = 1
    CONNECT BY LEVEL < 100;

Wyświetlenie wszystkich kodów walut zawartych w węzłach od drugiego do przedostatniego

SELECT EXTRACTVALUE(CXML,'//pozycja[' || LEVEL || ']/kod_waluty' ) FROM TBL_XML WHERE EXTRACTVALUE(CXML,'//pozycja[ position() = ' || LEVEL || ' and position() >=2 and position() <= last() -1 ]/kod_waluty' ) IS NOT NULL AND CID = 1
    CONNECT BY LEVEL < 100;

Policzenie ile wierszy w tabeli zawiera w polu CXML kod waluty dolara:

SELECT COUNT(*) FROM TBL_XML WHERE EXISTSNODE(CXML, '//pozycja[kod_waluty="USD"]') = 1;
wynik 1

piątek, 10 kwietnia 2020

Obsługa XML - część 1

Zacznijmy od stworzenia tabeli
CREATE TABLE TBL_XML
(
CID                                 NUMBER(6)       NOT NULL,
CXML                  SYS.XMLTYPE    NOT NULL,
UZYTKOWNIK            VARCHAR2(30 CHAR)      DEFAULT USER NOT NULL,
DATA_WSTAWIENIA DATE DEFAULT SYSDATE    NOT NULL
)

Dlaczego został wybrany typ XMLTYPE , a nie CLOB ?? Ponieważ umożliwia wstawienie do kolumny CXML tylko poprawnego składniowo.
Jako xml wezmę początek pliku XML z kursami walut ze strony NBP na dzień 3 kwietnia 2020, zawierający pierwsze pięć węzłów.

<tabela_kursow typ="A" uid="20a066">
  <numer_tabeli>066/A/NBP/2020</numer_tabeli>
  <data_publikacji>2020-04-03</data_publikacji>
  <pozycja>
    <nazwa_waluty>bat (Tajlandia)</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>THB</kod_waluty>
    <kurs_sredni>0,1287</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar amerykański</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>USD</kod_waluty>
    <kurs_sredni>4,2396</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar australijski</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>AUD</kod_waluty>
    <kurs_sredni>2,5527</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar Hongkongu</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>HKD</kod_waluty>
    <kurs_sredni>0,5469</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar kanadyjski</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>CAD</kod_waluty>
    <kurs_sredni>2,9956</kurs_sredni>
  </pozycja>
</tabela_kursow>

Wstawiamy dane do tabeli:

INSERT INTO TBL_XML (CID, CXML)
VALUES
( 1,  '<tabela_kursow typ="A" uid="20a066">
  <numer_tabeli>066/A/NBP/2020</numer_tabeli>
<data_publikacji>2020-04-03</data_publikacji>
  <pozycja>
    <nazwa_waluty>bat (Tajlandia)</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>THB</kod_waluty>
    <kurs_sredni>0,1287</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar amerykański</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>USD</kod_waluty>
    <kurs_sredni>4,2396</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar australijski</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>AUD</kod_waluty>
    <kurs_sredni>2,5527</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar Hongkongu</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>HKD</kod_waluty>
    <kurs_sredni>0,5469</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar kanadyjski</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>CAD</kod_waluty>
    <kurs_sredni>2,9956</kurs_sredni>
  </pozycja>
</tabela_kursow>')

Spróbujmy z pola XML pobrać kod waluty pierwszego węzła w sposób prawidłowy, aby wynik zapytania był typu VARCHAR2. Można do tego celu wykorzystać funkcje EXTRACT i EXTRACTVALUE. Ta druga jest lepsza co poniżej pokażemy:

Poniższe polecenie jest niepoprawne:
SELECT EXTRACT(CXML,'//pozycja[1]/kod_waluty' ) FROM TBL_XML;

Co nam zwróci - to zależy od edytora SQL.. w TOAD jest to tekst "THB. Ale jakiego rzeczywiście typu typu jest wynik powyższego zapytania ????

SELECT DUMP(EXTRACT(CXML,'//pozycja[1]/kod_waluty' )) FROM TBL_XML;

Otrzymamy:
Typ=58 Len=48: 128,68,239,156,252,127,0,0,192,142,121,1,178,1,0,0,32,3,128,1,178,1,0,..
Taki sam wynik otrzymamy dla zapytania:
SELECT DUMP(EXTRACT(CXML,'//pozycja[1]/kod_waluty/text()' )) FROM TBL_XML;

Co to jest typ 58 ?? wyjaśnienie znajduje się w pakiecie DBMS_TYPES:
TYPECODE_OPAQUE          PLS_INTEGER := 58;
W dokumentacji przeczytamy, że jest to typ abstrakcyjny (w znaczeniu nie można wykorzystywać w PL/SQL), zaimplementowany jako ciąg bajtów..
Niektóre edytory SQL potrafią sobie z tym poradzić i wtedy mamy mylne złudzenie, że zapytanie zwraca wartość tekstową..

Wymusimy konwersję na tekst:
SELECT CAST(EXTRACT(CXML,'//pozycja[1]/kod_waluty' ) AS VARCHAR2 (30 CHAR)) FROM TBL_XML;

Wynik:
THB

Jeśli wynusimy konwersję typu TYPECODE_OPAQUE do typu VARCHAR2 za pomocą funkcji GestStringVal()

SELECT EXTRACT(CXML,'//pozycja[1]/kod_waluty' ).GestStringVal() FROM TBL_XML;
Wynik:
THB
wynik typu TYPECODE_VARCHAR

SELECT EXTRACT(CXML,'//pozycja[1]/kod_waluty/text()' ).GestStringVal() FROM TBL_XML;
Wynik:
THB
Wynik typu TYPECODE_VARCHAR

Z funkcję EXTRACTVALUE parsowanie jest prostsze.

SELECT EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty' ) FROM TBL_XML;
i
SELECT EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty/text()' ) FROM TBL_XML;
zwraca:
THB

Zarówno
SELECT DUMP(EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty' ) ) FROM TBL_XML;
jak i
SELECT DUMP(EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty/text()' )) FROM TBL_XML;
zwraca wynik:
Typ=1 Len=3: 84,72,66
czyli długość 3 znaki, gdzie 84,72,66 to kody ASCII poszczególnych liter

W pakiecie DBMS_TYPES znajdziemy
TYPECODE_VARCHAR         PLS_INTEGER :=   1;

Tu jest mała pułapka, bo typ VARCHAR2 nie rozróżnia wartości NULL i pustego tekstu, a typ VARCHAR to rozróżnia. Jest ona niegroźna gdyż puste znaczniki i atrybuty zwracane są jako NULL.

Podsumowując do prostego parsowania najlepsza jest funkcja EXTRACTVALUE, ponieważ wyłuskuje wartość znacznika

Zabawy z Oracle SQL, PL/SQL i ODI

Blog programisty w Oracle PL/SQL

Szukaj na tym blogu