PostgreSQL XML

postgres_xml.sql

-- Fungsi xpath
xpath(path, xml_text)

-- examples: array
select xpath('/a', '<a><b>b1</b><b>b2</b></a>');
select xpath('/a/*', '<a><b>b1</b><b>b2</b></a>');
select xpath('/a/*/text()', '<a><b>b1</b><b>b2</b></a>');
-- pecah row
select unnest(xpath('/a/*/text()', '<a><b>b1</b><b>b2</b></a>'))::text;
-- atribut
select xpath('//@id', '<a><b id="foo">b1</b><b id="bar">b2</b></a>') ;

-- exact path
select xpath('/a/b/text()', '<a><b>bb</b><c>cc</c></a>');
-- ambil semua isi
select xpath('/a/*/text()', '<a><b>bc</b><c>cc</c></a>');
--> {bc,cc}

-- ambil text dari node pertama
select xpath('//b/text()', '<a><b>b1</b><b>b2</b></a>'); -- array
-- ambil text dari node pertama, ambil array yang kedua
select (xpath('//b/text()', '<a><b>b1</b><b>b2</b></a>'))[2]; 
-- ambil text dari node pertama, dengan atribut
select xpath('/a/b[@id="b2"]/text()', '<a><b>b1</b><b id="b2">b2</b></a>'); 

-- tipe data
-- gunakan data src ini
WITH src AS (
  SELECT
  '<data>
     <angka_int>10</angka_int>
     <angka_decimal>123.45</angka_decimal>
     <tanggal>2024-05-01</tanggal>
     <timestamp>2024-05-01 08:30:00</timestamp>
     <flag>true</flag>
     <kode>00123</kode>
   </data>'::xml AS x
)
-- integer
WITH src AS (...)
SELECT
  (xpath('/data/angka_int/text()', x))[1]::text::int AS nilai_int
FROM src;
-- numeric/decimal
WITH src AS (...)
SELECT
  (xpath('/data/angka_decimal/text()', x))[1]::text::numeric(10,2) AS nilai_decimal
FROM src;
-- date
WITH src AS (...)
SELECT
  (xpath('/data/tanggal/text()', x))[1]::text::date AS tgl
FROM src;
-- timestamp
WITH src AS (...)
SELECT
  (xpath('/data/timestamp/text()', x))[1]::text::timestamp AS ts
FROM src;
-- boolean
WITH src AS (...)
SELECT
  (xpath('/data/flag/text()', x))[1]::text::boolean AS is_valid
FROM src;
-- text cleaning/trimming
WITH src AS (...)
SELECT
  TRIM((xpath('/data/kode/text()', x))[1]::text) AS kode
FROM src;
-- integer: angka 0 didepan hilang
WITH src AS (...)
SELECT
  (xpath('/data/kode/text()', x))[1]::text::int AS kode_int
FROM src;

-- multivalue: array - numeric
SELECT
  unnest(xpath('/a/*/text()', '<a><b>10</b><b>20</b></a>'))::text::int AS nilai;
-- sum/aggregat
SELECT
  SUM((unnest(xpath('/a/*/text()', '<a><b>10</b><b>20</b></a>')))::text::int) AS total;

-- safe casting
SELECT
  NULLIF((xpath('/data/angka_decimal/text()', x))[1]::text, '')::numeric
FROM (
  SELECT '<data><angka_decimal></angka_decimal></data>'::xml x
) t;

-- Table: tabel_xml
CREATE TABLE IF NOT EXISTS tabel_xml (
    case_id VARCHAR(10) PRIMARY KEY,
    xml_output XML
);
INSERT INTO tabel_xml(case_id, xml_output) VALUES
('CASE001', '<kasus id="CASE001"><pasien><nama>Budi Santoso</nama><alamat>Jakarta</alamat></pasien><dokter id="DOC-01"><nama>dr. Sarah</nama><spesialisasi>Spesialis Jantung</spesialisasi></dokter><events><event><waktu>08:00:00</waktu><nama>Pendaftaran</nama><biaya>50000.00</biaya></event><event><waktu>09:00:00</waktu><nama>Cek Tensi</nama><biaya>25000.00</biaya></event><event><waktu>10:00:00</waktu><nama>Konsultasi</nama><biaya>150000.00</biaya></event></events></kasus>'),
('CASE002', '<kasus id="CASE002"><pasien><nama>Ani Wijaya</nama><alamat>Bandung</alamat></pasien><dokter id="DOC-02"><nama>dr. Anton</nama><spesialisasi>Spesialis Anak</spesialisasi></dokter><events><event><waktu>08:30:00</waktu><nama>Pendaftaran</nama><biaya>50000.00</biaya></event><event><waktu>11:00:00</waktu><nama>Tes Darah</nama><biaya>200000.00</biaya></event></events></kasus>');

-- daftar pasien
SELECT case_id,
    -- nama dokter
    (xpath('string(/kasus/dokter/nama)', xml_output))[1]::text AS nama_dokter
FROM tabel_xml;

-- daftar dokter
SELECT case_id,
    -- nama pasien
    (xpath('string(/kasus/pasien/nama)', xml_output))[1]::text AS nama_pasien
FROM tabel_xml;

-- penanganan
SELECT case_id,
    -- waktu
    unnest(xpath('/kasus/events/event/waktu/text()', xml_output))::text AS waktu,
    -- nama
    unnest(xpath('/kasus/events/event/nama/text()', xml_output))::text AS nama,
    -- biaya
    unnest(xpath('/kasus/events/event/biaya/text()', xml_output))::text AS biaya
FROM tabel_xml;

-- total biaya penanganan
SELECT a.case_id, SUM(biaya::numeric) total_biaya
FROM tabel_xml a
 LEFT JOIN (SELECT case_id,
                   unnest(xpath('/kasus/events/event/biaya/text()', xml_output))::text AS biaya
            FROM tabel_xml) b
  ON b.case_id=a.case_id
GROUP BY a.case_id;
-- atau dengan lateral
SELECT 
    a.case_id,
    SUM(b.biaya) AS total_biaya
FROM tabel_xml a
CROSS JOIN LATERAL (
    SELECT 
        unnest(xpath('/kasus/events/event/biaya/text()', xml_output))::text::numeric AS biaya
) b
GROUP BY a.case_id;

-- flaten
-- relational
SELECT
  case_id,
  (xpath('string(/kasus/pasien/nama)', xml_output))[1]::text AS nama_pasien,
  unnest(xpath('/kasus/events/event/nama/text()', xml_output))::text AS event
FROM tabel_xml;

-- Tugas
-- Buatkan tabel nf1, nf2, nf3


-- XML Format Handling
CREATE TABLE IF NOT EXISTS tabel_xmlstr (
    id INT PRIMARY KEY,
    xml_data XML
);
insert into tabel_xmlstr
select 1,'<trans><orderNo>0001</orderNo><orderDate>08/07/2014</orderDate><orders><item><itemName>item1</itemName><itemAmount>200</itemAmount><itemMeasures><measure>each</measure><measure>case</measure></itemMeasures></item><item><itemName>item2</itemName><itemAmount>100</itemAmount><itemMeasures><measure>each</measure><measure>case</measure></itemMeasures></item></orders></trans>' ;
-- Tugas
-- Buat tabel: orderno orderdate b.itemname b.itemamount b.itemmeasures c.measure