Hlavní navigace

PostgreSQL: XML co s ním 1. část

1. 11. 2016 11:49 (aktualizováno) | Michal Šalko

XML datové struktúry byly původně vyvinuty, jako výměnný formát dat mezi systémy. Taky jsou důležité jako datová uložiště pro aplikace, které mají data hodně strukturovaná. Bohužel někdy jsme nuceny i když neradi nacpat je do relačních databázích.

Jak na to v PostgreSQL ?

Zde pár věcí, které je nutné si všimnout při práci s XML daty v PostgreSQL.

Verifikace XML

Ačkoliv PostgreSQL umí pracovat s XML, vezte že je to kompositní datový typ a proto jak na sestavení, tak na interpretaci dat potřebuje funkce, které spomalují přístup k datům. Jde to dotáhnout brutálním nákupem hardwaru.

Obecně pro XML v PostreSQL se používá datový typ XML. Lze data ukládat i do datového typu TEXT, ale pak není jistota, jestli data jsou uložena v správném formátu XML. PostgreSQL vždy při každém ukládání nebo práci s daty je verifikuje, jestli jsou XML data ve správnem formátu. Asi nejednoduchším způsobem jak ověřit správnost XML dat je použít SQL příkaz, který se pokusí převést data v text na XML (<data>::XML) :

select
'<gc:data_basic xsi:schemalocation="http:// cesta .xsd" xmlns:gc="http://../xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <gc:data>158.00</gc:data>
    <gc:data1>5</gc:data1>

</gc:data_basic>'::xml 

V případě, že je vše OK, SQL vám vrátí :

     xml
------------------------- ...
 <gc:data_basic xsi:schemaLocation= ... 

V opačném případě Vám vrátí :

ŘÁDKA 2: '<gc:data_basic xsi:schemaLocation="http://...
         ^
DETAIL:  line 8: Opening and ending tag mismatch: data_basic line 1 and data_basic
</gc:data_basic>
                        ^
line 8: chunk is not well balanced
</gc:data_basic>
                        ^ 

Pak nezůstane nic jiného než ladit XML obsah. (Zde je zahajovací a koncový tag odlišný.)

Namísto <data>::XML lze použít i funkci cast( <data> as xml).

Způsob uložení dat XML v tabulkách

V tabulkách se XML uloží jako datový typ sloupce. Je důležité jaká data se pak do XML sloupce ukádají. Buď všechno jsou to XML stajného typu, nebo ne.

Nejlépe se to pozná jak je definovaný správný XSD (XML Schema definition) v záhlaví XML dokumentu. Ale mohou být i bez něj a pak je to sázka do loterie.

Když jsou stejné pro všechny řádky tabulky, tak se jedná o homogenní XML data a je možné přistupovat k datům tabulky jako k celku. Když ne jsou to nehomogénní XML data a pak musíte přistupovat ke každému řádku individuálně.

Jako příklad mohu uvést

1. Nehomogénní množina – Číselníky

Obvykle v praxi jsem se setkal s podobou, kdy se takto uložili číselníky, které se pak používali k verifikaci hodnot v externích XML souborech. Byla to dichtiotomie, protože stejné číselníky se museli použít i samotné db pro zabespečení referenční integrity i jako zdroj dat. Ale o tom jindy.

Takže v tabulce každý řádek v sloupci hodnota obsahuje jiná schémata dle kterých byl definovaný  typ dat.

id
integer
typ
varchar(40)
hodnota
xml
1 hmotnost <hodnota>
2 čas <cas>

SQL dotaz pro vytvoření tabulky a příklad dat:

create table jednotka
(id serial primary key,
 typ varchar(80) not null unique,
 hodnota xml not null);

-- Priklad dat
insert into jednotka
(typ,hodnota)
 values
 (
 'cas',
 '<cas>
   <jednotka>
    <nazev>sekunda</nazev><zkr>s</zkr><koeficient>1</koeficient>
   </jednotka>
   <jednotka>
    <nazev>minuta</nazev><zkr>min</zkr><koeficient>60</koeficient>
   </jednotka>
   <jednotka>
    <nazev>den (střední sluneční)</nazev><zkr>d</zkr><koeficient>86400</koeficient>
   </jednotka>
   <jednotka>
    <nazev>sol (střední Mars)</nazev><zkr>sol</zkr><koeficient>88775</koeficient>
   </jednotka>
  </cas>':: XML
  ); 

Po vložení dat je data potřeba i z SQL vytáhnout. Na to musíme udělat několik věcí :

  1. Separovat řádky XML do samostatních řádků.
  2. Separovat obsah tagů do sloupců a vrátit je v správnem datovém typu.

Jde to použitím funkcí :

  • xpath(<cesta>,<hodnota>) – z XML dostane obsah učitého tagu v dané formě. Výsledek je uložený v poli hodnot.
  • unnset(<pole>) – Rozloží pole hodnot na jednotlivé elementy do řádků
  • array_to_string(<pole>) – Převede pole na textový řetězec
select
array_to_string(xpath('/jednotka/nazev/text()'::text, radek),',') as nazev,
array_to_string(xpath('/jednotka/zkr/text()'::text, radek),',') as zkr,
array_to_string(xpath('/jednotka/koeficient/text()'::text, radek),',')::numeric as koeficient
from
(select unnest(xpath('/cas/jednotka'::text,hodnota)) as radek
 from jednotka where typ = 'cas') a; 

Výsledek :

nazev zkr koeficient
sekunda s 1
minuta min 60
den (střední sluneční) d 86400
sol (střední Mars) sol 88775

2. Homogenní množina

Zde je situace, že data chodí z externího zdroje v csv formátu, ale poslední sloupec je z důvodu struktury dat ve formátu xml. Otázka na místě by byla proč veškerá data nejsou ve formátu XML ? Zde je to pouze z důvodu demostrace technologie.

id place meteo_data meteo_vals
1 Veveri 21.3.2016 <meteo>
2 Prague 21.3.2016 <meteo>

SQL příkaz a příklad vložení dat

-- Vytvoreni tabulky
create table weather_day
(id serial primary key,
 place varchar(80) not null,
 meteo_date date not null,
 meteo_vals xml not null,
 unique(place,meteo_date));

-- Vložení dat za Vevěří a Prahu 21.3.2016
insert into weather_day
(place,meteo_date,meteo_vals)
values
('Veveri',
to_date('21.3.2016','DD.MM.YYYY'),
'<meteo>
<hourly><time>9</time><temp>6</temp><feels>3</feels><humidity>61</humidity></hourly>
<hourly><time>10</time><temp>7</temp><feels>5</feels><humidity>55</humidity></hourly>
<hourly><time>11</time><temp>8</temp><feels>5</feels><humidity>52</humidity></hourly>
<hourly><time>12</time><temp>8</temp><feels>5</feels><humidity>53</humidity></hourly>
<hourly><time>13</time><temp>8</temp><feels>6</feels><humidity>52</humidity></hourly>
<hourly><time>14</time><temp>9</temp><feels>6</feels><humidity>52</humidity></hourly>
<hourly><time>15</time><temp>9</temp><feels>6</feels><humidity>52</humidity></hourly>
<hourly><time>16</time><temp>8</temp><feels>6</feels><humidity>55</humidity></hourly>
</meteo>'::XML),
('Prague',
to_date('21.3.2016','DD.MM.YYYY'),
'<meteo>
<hourly><time>9</time><temp>5</temp><feels>2</feels><humidity>73</humidity></hourly>
<hourly><time>10</time><temp>6</temp><feels>3</feels><humidity>69</humidity></hourly>
<hourly><time>11</time><temp>7</temp><feels>4</feels><humidity>65</humidity></hourly>
<hourly><time>12</time><temp>7</temp><feels>5</feels><humidity>61</humidity></hourly>
<hourly><time>13</time><temp>8</temp><feels>6</feels><humidity>59</humidity></hourly>
<hourly><time>14</time><temp>8</temp><feels>6</feels><humidity>59</humidity></hourly>
<hourly><time>15</time><temp>8</temp><feels>6</feels><humidity>58</humidity></hourly>
<hourly><time>16</time><temp>8</temp><feels>6</feels><humidity>59</humidity></hourly>
</meteo>'::XML); 

Pro výběr dat použijeme stejne SQL příkazy + navíc kombinaci s klasickými sloupci.

select
place,
meteo_date +(array_to_string(xpath('/hourly/time/text()'::text, radek),',')::text||' hours')::interval as hour,
array_to_string(xpath('/hourly/temp/text()'::text, radek),',')::integer as temp,
array_to_string(xpath('/hourly/feels/text()'::text, radek),',')::integer as feels,
array_to_string(xpath('/hourly/humidity/text()'::text, radek),',')::integer as humidity
from
(select unnest(xpath('/meteo/hourly'::text,meteo_vals)) as radek,meteo_date,place
 from weather_day ) a; 
place hour temp feels humidity
Veveri 2016–03–21 09:00:00 6 3 61
Veveri 2016–03–21 10:00:00 7 5 55
Veveri 2016–03–21 11:00:00 8 5 52
Veveri 2016–03–21 12:00:00 8 5 53
Veveri 2016–03–21 13:00:00 8 6 52
Veveri 2016–03–21 14:00:00 9 6 52
Veveri 2016–03–21 15:00:00 9 6 52
Veveri 2016–03–21 16:00:00 8 6 55
Prague 2016–03–21 09:00:00 5 2 73
Prague 2016–03–21 10:00:00 6 3 69
Prague 2016–03–21 11:00:00 7 4 65
Prague 2016–03–21 12:00:00 7 5 61
Prague 2016–03–21 13:00:00 8 6 59
Prague 2016–03–21 14:00:00 8 6 59
Prague 2016–03–21 15:00:00 8 6 58
Prague 2016–03–21 16:00:00 8 6 59

Tak tolik v 1. části. Příště si řekneme jak xpath pro s definovaným XSD a jako zakomponovat tento typ dat do relačného světa.

Přidávat nové názory je zakázáno.

Lupa.cz: Kdo pochopí vtip, může jít do ČT vyvíjet weby

Kdo pochopí vtip, může jít do ČT vyvíjet weby

120na80.cz: 5 nejčastějších mýtů o kondomech

5 nejčastějších mýtů o kondomech

Měšec.cz: Kdy vám stát dá na stěhování 50 000 Kč?

Kdy vám stát dá na stěhování 50 000 Kč?

DigiZone.cz: Velká cena v Abú Dhabí: 131 ti­síc diváků

Velká cena v Abú Dhabí: 131 ti­síc diváků

120na80.cz: Horní cesty dýchací. Zkuste fytofarmaka

Horní cesty dýchací. Zkuste fytofarmaka

Lupa.cz: Propustili je z Avastu, už po nich sahá ESET

Propustili je z Avastu, už po nich sahá ESET

120na80.cz: Co všechno ovlivňuje ženskou plodnost?

Co všechno ovlivňuje ženskou plodnost?

DigiZone.cz: ČRo rozšiřuje DAB do Berouna

ČRo rozšiřuje DAB do Berouna

Vitalia.cz: Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Podnikatel.cz: Udávání kvůli EET začalo

Udávání kvůli EET začalo

Podnikatel.cz: Chtějte údaje k dani z nemovitostí do mailu

Chtějte údaje k dani z nemovitostí do mailu

Vitalia.cz: Když přijdete o oko, přijdete na rok o řidičák

Když přijdete o oko, přijdete na rok o řidičák

Vitalia.cz: Paštiky plné masa ho zatím neuživí

Paštiky plné masa ho zatím neuživí

Podnikatel.cz: K EET. Štamgast už peníze na stole nenechá

K EET. Štamgast už peníze na stole nenechá

Lupa.cz: Co se dá měřit přes Internet věcí

Co se dá měřit přes Internet věcí

Vitalia.cz: Potvrzeno: Pobyt v lese je skvělý na imunitu

Potvrzeno: Pobyt v lese je skvělý na imunitu

Měšec.cz: Jak vymáhat výživné zadarmo?

Jak vymáhat výživné zadarmo?

DigiZone.cz: Recenze Westworld: zavraždit a...

Recenze Westworld: zavraždit a...

Vitalia.cz: Pamlsková vyhláška bude platit jen na základkách

Pamlsková vyhláška bude platit jen na základkách

Vitalia.cz: Jsou čajové sáčky toxické?

Jsou čajové sáčky toxické?