XML

Ushbu qo'llanmada PostgreSQLda XML hujjatlarini saqlash va boshqarish uchun o'rnatilgan XML ma'lumot turidan qanday foydalanishni o'rganasiz.

PostgreSQL XML ma'lumot turiga umumiy nuqtai

PostgreSQL standart XML hujjatlarini va fragmentlarini saqlash uchun maxsus XML ma'lumot turini taqdim etadi.

Ustunni XML turi bilan e'lon qilish:

column_name XML

Oddiy TEXT turiga qaraganda XML turidan foydalanishning afzalliklari:

1. Sintaksis tekshiruvi (Type Safety): PostgreSQL kiritilayotgan XML hujjati to'g'ri shakllanganligini (well-formed XML) avtomatik tekshiradi.
2. O'rnatilgan funksiyalar: XPath orqali elementlarni qidirish, o'qish va o'zgartirish uchun qulay ichki funksiyalar mavjud.

Namunaviy jadval va ma'lumot kiritish

XML ma'lumotlarini saqlash uchun xproducts jadvalini yaratamiz:

DROP TABLE IF EXISTS xproducts CASCADE;

CREATE TABLE xproducts (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  data XML
);

XML ma'lumotlarini kiritish (XMLPARSE)

Matnni XML hujjatiga aylantirish uchun XMLPARSE(DOCUMENT '...') funksiyasidan foydalaniladi:

INSERT INTO xproducts (data)
VALUES
  (
    XMLPARSE(
      DOCUMENT '<?xml version="1.0" encoding="UTF-8"?>
<product>
    <name>Samsung Galaxy S24</name>
    <price>999.99</price>
    <safety_stock>10</safety_stock>
</product>'
    )
  ),
  (
    XMLPARSE(
      DOCUMENT '<?xml version="1.0" encoding="UTF-8"?>
<product>
    <name>Apple iPhone 15</name>
    <price>1099.99</price>
    <safety_stock>20</safety_stock>
</product>'
    )
  ),
  (
    XMLPARSE(
      DOCUMENT '<?xml version="1.0" encoding="UTF-8"?>
<product>
    <name>Huawei Mate 60</name>
    <price>899.99</price>
    <safety_stock>30</safety_stock>
</product>'
    )
  );

XPath yordamida XML ma'lumotlarini o'qish

xpath() funksiyasi XML hujjatidagi kerakli tugunlarni XPath ifodasi bo'yicha ajratib oladi va XML massivi (xml[]) ko'rinishida qaytaradi:

SELECT
  xpath('/product/name/text()', data) AS name
FROM
  xproducts;

Natija:

          name          
------------------------
 {"Samsung Galaxy S24"}
 {"Apple iPhone 15"}
 {"Huawei Mate 60"}

Olingan ma'lumotlarni SQL turlariga o'girish (Casting)

xpath() massiv qaytargani uchun birinchi elementni [1] orqali olamiz va kerakli ma'lumot turiga o'giramiz:

SELECT
  (xpath('/product/name/text()', data))[1]::TEXT AS name,
  (xpath('/product/price/text()', data))[1]::TEXT::DEC AS price,
  (xpath('/product/safety_stock/text()', data))[1]::TEXT::INT AS safety_stock
FROM
  xproducts;

Natija:

        name        |  price  | safety_stock 
--------------------+---------+--------------
 Samsung Galaxy S24 |  999.99 |           10
 Apple iPhone 15    | 1099.99 |           20
 Huawei Mate 60     |  899.99 |           30

XML ustunini indekslash

XPath ifodasi natijasiga indeks o'rnatish orqali XML ichidagi ma'lumotlar bo'yicha qidiruvni tezlashtirish mumkin:

CREATE INDEX idx_xproducts_name 
ON xproducts 
USING BTREE (CAST(xpath('/product/name', data) AS TEXT[]));

Xulosa

• XML ma'lumot turi XML hujjatlarining to'g'ri tuzilishini (valid XML) kafolatlaydi.
• XMLPARSE(DOCUMENT '...') matnni XML ga aylantiradi.
• xpath() funksiyasi XPath ifodalari orqali istalgan XML elementlari va qiymatlarini o'qib olish imkonini beradi.