GOV/Webservice/Oracle
< GOV • Webservice
Zur Navigation springen
Zur Suche springen
Auslesen von GOV-Einträgen mit Oracle und dem GOV-Simple-WebService
Tabellen
Item-Tabelle
create sequence seq_gov_data
start with 1
increment by 1
order;CREATE TABLE f_gov_data (
lfd_id number(10),
item_id varchar2(40),
item_data XMLTYPE,
item_childs XMLTYPE,
lfd_id_parent number(10),
verarbeitet varchar2(1)
);create unique index pk_f_gov_data on f_gov_data (lfd_id);
alter table f_gov_data add primary key (lfd_id) using index pk_f_gov_data;Kind-Objekt-Tabelle
CREATE TABLE f_gov_child (
child_id number(10),
child_item_id varchar2(40),
parent_item_id varchar2(40),
lfd_id_parent number(10),
verarbeitet varchar2(1)
);create unique index pk_f_gov_child on f_gov_child (child_id);
alter table f_gov_child add primary key (child_id) using index pk_f_gov_child;create sequence seq_gov_child
start with 1
increment by 1
order;Funktion zum Auslesen eines GOV-Eintrags
CREATE OR REPLACE FUNCTION GOV_GET_ITEMID (p_aktion in CHAR, p_name IN CHAR)
RETURN XMLTYPE
AS
l_envelope CLOB;
l_xml XMLTYPE;
l_result XMLTYPE;
BEGIN
l_envelope := '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
<soap:Body>
<' || p_aktion || ' xmlns="http://gov.genealogy.net/ws">
<itemId xmlns="">' || p_name || '</itemId>
</' || p_aktion || '>
</soap:Body>
</soap:Envelope>';
-- Request abschicken und XML-Response holen
l_xml := APEX_WEB_SERVICE.make_request(
p_url => 'http://gov.genealogy.net/services/SimpleService',
p_envelope => l_envelope
);
RETURN l_xml;
END;
/Prozedur zum Auslesen der Kind-Objekte
create or replace PROCEDURE gov_get_child
AS
v_anz_saetze number(8) := 1;
v_satznr number(10) := 0;
v_lfd_id number (8) := 0;
v_item_id varchar2(40):= ' ';
CURSOR c_satz IS
SELECT lfd_id,
item_id
from f_gov_data
where verarbeitet is null
and item_data is null
ORDER BY lfd_id;
BEGIN
SELECT count(*) INTO v_anz_saetze from f_gov_data where verarbeitet is null and item_data is null;
OPEN c_satz;
LOOP
FETCH c_satz INTO v_lfd_id , v_item_id;
v_satznr := v_satznr + 1;
EXIT WHEN v_satznr > v_anz_saetze;
merge into f_gov_data f
using
(
select
lfd_id,
item_id,
gov_get_itemId ('getObject',item_id) as item_data,
gov_get_itemId ('getChildObjects',item_id) as item_childs
from f_gov_data
where lfd_id = v_lfd_id
) s
on (f.lfd_id = s.lfd_id)
when matched then update set
f.item_data = s.item_data,
f.item_childs = s.item_childs
;
commit;
END LOOP;
CLOSE c_satz;
END;
/Prinzip des Auslesens
erster Eintrag (Wurzel)
insert into f_gov_data
(lfd_id,
item_id,
item_data,
item_childs
)
values
(seq_gov_data.nextval,
'objekt_123',
gov_get_itemId ('getObject','objekt_123'),
gov_get_itemId ('getChildObjects','objekt_123')
);Kinder der neuen Sätze ermitteln
insert into f_gov_child
(child_id, child_item_id, parent_item_id, lfd_id_parent)
select
seq_gov_child.nextval AS child_id,
item AS child_item_id,
item_id AS parent_item_id,
lfd_id AS lfd_id_parent
from
(
select distinct lfd_id, t.item_id, y.item
from f_gov_data t,
XMLTable (XMLNamespaces('http://gov.genealogy.net/ws' AS "ns3"),
'//out'
passing item_childs
COLUMNS item XMLType path 'ns3:item'
) x,
XMLTable (XMLNamespaces('http://gov.genealogy.net/ws' AS "ns3"),
'ns3:item'
passing x.item
COLUMNS item varchar2(39) path '.'
) y
where verarbeitet is null
and item_data is not null
);Und wieder den ersten Schritt ausführen - mit den Daten aus Schritt zwei.
Parsen der Ergebnisse
letzte Änderung und Koordinaten
select item_id as govid,
to_date(substr(last_modification_c,1,10) || ' '
|| substr(last_modification_c,12,8),'yyyy-mm-dd hh24:mi:ss') as last_modification,
id,
artkoord,
CASE WHEN (breite is not null)
then cast(replace(breite,'.',',') as number(18,14))
else null
end AS breite,
CASE WHEN (laenge is not null)
then cast(replace(laenge,'.',',') as number(18,14))
else null
end AS laenge,
to_number(hoehe) as hoehe,
count (*) over (partition by item_id order by rownum) as lfd_id
from f_gov_data t,
XMLTable (XMLNamespaces('http://gov.genealogy.net/ws' AS "ns3",
'http://gov.genealogy.net/data' AS "ns2"
),
'//out'
passing t.item_data
COLUMNS last_modification_c varchar2(30 char) path '@last-modification',
id varchar2(30 char) path '@id',
breite varchar2(100) path 'ns2:position/@lat',
laenge varchar2(100) path 'ns2:position/@lon',
artkoord varchar2(1) path 'ns2:position/@type',
hoehe varchar2(10) path 'ns2:position/@height'
) yName mit Datum
select govid, lfd_id, c_n, x_q,
case -- nur eine Jahreszahl
when ((c_y is not null) ) then c_y
-- nur Jahr für Beginn und Ende
when ((c_by is not null) and (c_ey is not null)) then c_by || ' - '
|| c_ey
-- nur Jahr für Beginn
when ((c_by is not null) ) then c_by || ' - '
-- nur Jahr für Ende
when ((c_ey is not null) ) then '- '
|| c_ey
-- Datum für Beginn und Ende mit Formatierung (Trunkierung)
when ((c_b is not null) and (c_e is not null)) then to_char(to_date(c_b, 'J'), f_bp) || ' - '
|| to_char(to_date(c_e, 'J'), f_ep)
-- Datum für Beginn
when ((c_b is not null) ) then to_char(to_date(c_b, 'J'), f_bp) || ' - '
-- Datum für Ende
when ((c_e is not null) ) then ' - '
|| to_char(to_date(c_e, 'J'), f_ep)
end as c_z,
case
when (c_y is not null) then to_date(c_y || '-01-01','YYYY-MM-DD')
when (c_by is not null) then to_date(c_by || '-01-01','YYYY-MM-DD')
when (c_b is not null) then to_date(c_b, 'J')
end as d_b,
case
when (c_y is not null) then to_date(c_y || '-01-01','YYYY-MM-DD')
when (c_ey is not null) then to_date(c_ey || '-01-01','YYYY-MM-DD')
when (c_e is not null) then to_date(c_e, 'J')
end as d_b
from
(
select item_id as govid, c_n, c_by, c_ey, c_y, c_b,
-- Formatierungsmaske
case when (c_bp = '0') then 'YYYY'
when (c_bp = '1') then 'YYYY-MM'
when (c_bp = '2') then 'YYYY-MM-DD'
else null
end as f_bp, c_bp,
to_date(c_e, 'J') as d_e, c_e,
case when (c_ep = '0') then 'YYYY'
when (c_ep = '1') then 'YYYY-MM'
when (c_ep = '2') then 'YYYY-MM-DD'
else null
end as f_ep, c_ep,
c_q as x_q,
-- XML im Text umwandeln für Trace
-- y.c_q.getStringVal(),
count (*) over (partition by item_id order by rownum) as lfd_id
from f_gov_data t,
XMLTable (XMLNamespaces('http://gov.genealogy.net/data' AS "ns2"),
'//out/ns2:name'
passing t.item_data
COLUMNS c_n varchar2(100 char) path '@value',
c_by varchar2(4 char) path '@begin-year',
c_ey varchar2(4 char) path '@end-year',
c_y varchar2(4 char) path '@year',
c_b varchar2(8 char) path 'ns2:timespan/ns2:begin/@jd',
c_bp varchar2(1) path 'ns2:timespan/ns2:begin/@precision',
c_e varchar2(8 char) path 'ns2:timespan/ns2:end/@jd',
c_ep varchar2(1) path 'ns2:timespan/ns2:end/@precision',
c_q xmltype path '.'
) y
)