Advertisement
Guest User

Untitled

a guest
Apr 16th, 2019
97
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 2.02 KB | None | 0 0
  1. DECLARE
  2.    p_x      xmltype := xmltype(
  3. '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  4. <policyInput>
  5.    <policyHolder>
  6.        <birthDate>1988-06-12</birthDate>
  7.        <gender>S</gender>
  8.        <nationalIdentificationNumber>111222333477</nationalIdentificationNumber>
  9.        <personName>ASDF</personName>
  10.        <personSurname>TESTF</personSurname>
  11.        <personType>OBČAN</personType>
  12.        <personTypeInsurance2Id>5</personTypeInsurance2Id>
  13.        <vatRegistered>Y</vatRegistered>
  14.    </policyHolder>
  15. </policyInput>');
  16.  
  17.    l_bday     VARCHAR2(100);
  18.    l_gender   VARCHAR2(100);
  19.    l_nid      VARCHAR2(100);
  20.    l_pname    VARCHAR2(100);
  21.    l_psname   VARCHAR2(100);
  22.    l_ptype    VARCHAR2(100);
  23.    l_ptid     VARCHAR2(10);
  24.    l_vatreg   VARCHAR2(2);
  25.    
  26. BEGIN
  27.    SELECT xt.*
  28.    INTO
  29.         l_bday
  30.       , l_gender
  31.       , l_nid
  32.       , l_pname
  33.       , l_psname
  34.       , l_ptype
  35.       , l_ptid
  36.       , l_vatreg
  37.    FROM xmltable(
  38.            'policyInput/policyHolder'
  39.            passing p_x
  40.            columns
  41.            birthDate VARCHAR2(100) PATH 'birthDate',
  42.            gender VARCHAR2(100) PATH 'gender',
  43.            nationalIdentificationNumber VARCHAR2(100) PATH 'nationalIdentificationNumber',
  44.            personName VARCHAR2(100) PATH 'personName',
  45.            personSurname VARCHAR2(100) PATH 'personSurname',
  46.            personType VARCHAR2(100) PATH 'personType',
  47.            personTypeInsurance2Id VARCHAR2(100) PATH 'personTypeInsurance2Id',
  48.            vatRegistered VARCHAR2(2) PATH 'vatRegistered'
  49.            ) xt;
  50.            
  51. INSERT INTO OSEBA VALUES (
  52.         l_bday
  53.       , l_gender
  54.       , l_nid
  55.       , l_pname
  56.       , l_psname
  57.       , l_ptype
  58.       , l_ptid
  59.       , l_vatreg);
  60.  
  61.    DBMS_OUTPUT.put_line(l_bday);
  62.    DBMS_OUTPUT.put_line(l_gender);
  63.    DBMS_OUTPUT.put_line(l_nid);
  64.    DBMS_OUTPUT.put_line(l_pname);
  65.    DBMS_OUTPUT.put_line(l_psname);
  66.    DBMS_OUTPUT.put_line(l_ptype);
  67.    DBMS_OUTPUT.put_line(l_ptid);
  68.    DBMS_OUTPUT.put_line(l_vatreg);
  69. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement