GOV/Webservice/Oracle

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'
          ) y

Name 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
)