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?

1.  Tworzona jest w pamięci tabela aliasowana przez xt
2. Każdy wiersz tabeli xt to powtarzający się element  pozycja, opisujący parametry waluty
3. W nawiasie po słowie kluczowym XMLTABLE podaje się ścieżkę do analizowanego elementu
4. Po słowie kluczowym PASSING podaje się nazwę pola zawierającego XML
5. 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
6. Wyrażenie xt.* zwraca kolumny zdefiniowane w mapowaniu

Uwagi:

1. 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  
2. Jeśli zdefiniujemy  typ o niewystarczającej długości, to podczas mapowania teksty zostaną obcięte
3. Jedli w danym elemencie nie wystąpi ścieżka z mapowania, wtedy dla odpowiadającej temu mapowaniu kolumny  zwracana jest wartość NULL
4. W przypadku zmiany kolumny lub dodania nowych pól, modyfikacja istniejącego zapytania jest banalna

    

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_XM WHERE  EXISTSNODE(CXML, '//pozycja[kod_waluty="USD"]') = 1;
wynik 1
 
 

   

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łow.

<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 potrafia 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óznia wartośći NULL i pustego tekstu, a typ VARCHAR to rozróznia.  Jest ona niegrożna gdyż puste znaczniki i atrybuty zwracane sa jako NULL.

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

Dzielimy kwotę na banknoty i monety

W jaki sposób  w SQL można rozbić dana kwotę na minimalną liczbę banknotów i monet??. To jest proste przy wykorzystaniu niezapomnianej klauzuli MODEL, kod przypomina prosty arkusz w MS Excel. Kwota, która ns interesuje wstawiana jest zamiast  przykładowej zielonej kwoty w kolorze zielonym Polecam analizę tego zapytania, ma kilka smaczków takich jak:

• podzapytanie ze złączeniem kartezjańskim
• ciekawy warunek stopu w iteracji
• indeks do iteracji liczony  w funkcji analitycznej

SELECT *
  FROM ( SELECT KWOTA,
                NOMINAL,
                LICZBA_NOMINALOW,
                RESZTA
           FROM (   SELECT LICZBA * MNOZNIK                                       NOMINAL,
                           ROW_NUMBER( ) OVER (ORDER BY LICZBA * MNOZNIK DESC)    LP
                      FROM( SELECT 1 AS LICZBA FROM DUAL
                            UNION
                            SELECT 2 FROM DUAL
                            UNION
                            SELECT 5 FROM DUAL),
                           (SELECT 100 AS MNOZNIK FROM DUAL
                            UNION
                            SELECT 10 FROM DUAL
                            UNION
                            SELECT 1 FROM DUAL
                            UNION
                            SELECT 0.1 FROM DUAL
                            UNION
                            SELECT 0.01 FROM DUAL)
                  ORDER BY 1 DESC )
         MODEL
             DIMENSION BY( LP - 1 LP )
             MEASURES( NOMINAL,
                       15329.98 KWOTA,
                       0 LICZBA_NOMINALOW,
                       1 RESZTA,
                       0 Z )
             RULES
             ITERATE( 100 ) UNTIL (RESZTA[ITERATION_NUMBER] = 0)
             (
                 UPSERT
                 KWOTA [ITERATION_NUMBER] =
                     CASE ITERATION_NUMBER
                         WHEN 0 THEN TRUNC( KWOTA[0], 2 )
                         ELSE RESZTA[ITERATION_NUMBER - 1]
                     END,
                 LICZBA_NOMINALOW [ITERATION_NUMBER] =
                     TRUNC(
                         KWOTA[ITERATION_NUMBER] / NOMINAL[ITERATION_NUMBER] ),
                 RESZTA [ITERATION_NUMBER] =
                       KWOTA[ITERATION_NUMBER]
                     -   NOMINAL[ITERATION_NUMBER]
                       * LICZBA_NOMINALOW[ITERATION_NUMBER] ) )
 WHERE LICZBA_NOMINALOW <> 0

Gdzie jest Anna Kowalska??

Tworzymy sobie tabelę jak poniżej
CREATE TABLE KLIENCI
 (
            ID_KLIENTA NUMBER(10) NOT NULL,   
            IMIE  VARCHAR2 (50 CHAR)  NOT NULL,
            NAZWISKO VARCHAR2 (100 CHAR) NOT NULL
...................
) 
Czy   to już wystarczy do poprawnego wyszukiwania klientów np Anny Kowalskiej?? Jeszcze niestety nie.
W jaki sposób mogą zostać zapisane te dane:
Anna Kowalska
ANNA KOWALSKA
AnnA  KoWalskA
anna  Kowalska            
   Anna KOwalska

Możliwości jest wiele.  Problem opisany przez mnie dotyczy standaryzacji danych tekstowych...Ten aspekt należy ustalić już sobie na etapie projektowania systemu. - w jaki sposób standaryzujemy istotne dane tekstowe, a w tym przypadku, czy imiona i nazwiska klientów zapisujemy:

• dużymi  literami
• bez niewidocznych znaków wiodących i kończących  w polach imienia i nazwiska oraz znaków interpunkcyjnych
• czy dodajemy dodatkowe sprawdzenia na poziomie bazy danych np.
• CHECK (IMIE = UPPER(TRIM(IMIE) ) )
• lub CHECK (IMIE = INITCAP(TRIM(IMIE) ) ) 
• znaki interpunkcyjne  można  wyłowić za pomocą wyrażeń regularnych

Do czego to prowadzi??

• Otrzymujemy złożone i wolne zapytania jak np. poniżej

SELECT * FROM KLIENCI WHERE  REGEXP_LIKE(TRIM(UPPER( REGEXP_REPLACE(IMIE, '[^[:alpha:]]', ''))),  '^AN(N|K|I)A$') AND TRIM(UPPER(REGEXP_REPLACE(NAZWISKO, '[^[:alpha:]]', ''))) = 'KOWALSKA'

• Instrukcja REGEXP_REPLACE(IMIE, '[^[:alpha:]]', '')  służy do wycinania znaków, które nie są  literami (dla złożonych wieloczłonowych nazwisk może być problem)
• Uważny Czytelnik dostrzeże, że funkcja TRIM jest niepotrzebna i spełnia tylko rolę przejaskrawiającą
• Nawet takie poplątane zapytania mogą pokrywać jedynie większość danych, ale może istnieć wiele błędnych przypadków, których nie jesteśmy w stanie przewidzieć

Co nam da standaryzacja??

• Wyszukiwanie będzie działać szybciej, jeśli będą założone zwykłe indeksy po imieniu i nazwisku, stosowanie funkcji konwertujących będzie przeczesywać cała tabelę i generować niepotrzebne obciążenie
• Zapytania staną się prostsze i szybsze. Zapytanie poniższe będzie wystarczające:

SELECT * FROM KLIENCI WHERE IMIE = 'ANNA' AND NAZWISKO = 'KOWALSKA'

•  Indeks po nazwisku  zapewni range scan zamiast full scan
• Wyniki raportów będą wymagały mniejszej standaryzacji
• Standaryzacja  zmniejsza koszty utrzymania systemu

A co można jeszcze zrobić w tym przypadku??

• zesłownikować imiona, żeby była tylko Anna, a nie Anka lub Ańka  lub Ania.  Najczęściej występujące  imiona da się zebrać w słowniku , a na  formatce przy polu imię można wprowadzić checbox ,., którego odznaczenie wyłączy walidację imion ze słownikiem. Ciągle pojawiają się nowe serialowe inspiracje oraz imiona zagraniczne, których  nie da się  uwzględnić. Ale mimo wszystko są to sytuacje wyjątkowe