XMLTABLE в Oracle SQL — как извлекать данные из XML-структур

XMLTABLE в Oracle SQL. Введение

XMLTABLE в Oracle SQL — это мощный оператор, позволяющий преобразовать XML-данные в табличный вид. Он используется в SELECT-запросах, работает с XMLType и XPath-выражениями. XMLTABLE особенно полезен при работе с XML-файлами, веб-сервисами и интеграцией. В этой статье ты найдёшь 50 практических примеров применения XMLTABLE.

🖋️ Синтаксис XMLTABLE

SELECT ...
FROM XMLTABLE (
  'XPath-выражение'
  PASSING xml_источник
  COLUMNS
    alias тип PATH 'xpath_столбца',
    ...
);

• PASSING — указывает XML-данные
• COLUMNS — определяет структуру табличного результата
• Поддерживает множественные уровни и вложенность

🔄 Где используется XMLTABLE

• Извлечение данных из XMLType
• Интеграция с внешними API и XML-файлами
• Построение отчётов и преобразование форматов
• Обработка вложенных структур

📊 Примеры использования XMLTABLE в Oracle SQL (1–5 из 50)

1. Простая выборка из XML:

SELECT *
FROM XMLTABLE(
  '/employees/employee'
  PASSING XMLTYPE('<employees><employee><id>1</id><name>Ivan</name></employee></employees>')
  COLUMNS
    emp_id   NUMBER PATH 'id',
    emp_name VARCHAR2(100) PATH 'name'
);

2. Обработка атрибутов:

SELECT *
FROM XMLTABLE(
  '/items/item'
  PASSING XMLTYPE('<items><item id="101" name="Keyboard"/></items>')
  COLUMNS
    id   NUMBER PATH '@id',
    name VARCHAR2(50) PATH '@name'
);

3. Работа с вложенными тегами:

SELECT *
FROM XMLTABLE(
  '/orders/order'
  PASSING XMLTYPE('<orders><order><number>1</number><customer><name>Anna</name></customer></order></orders>')
  COLUMNS
    order_num  NUMBER PATH 'number',
    cust_name  VARCHAR2(100) PATH 'customer/name'
);

4. Использование псевдонима документа:

SELECT *
FROM XMLTABLE(
  '/catalog/product'
  PASSING XMLTYPE('<catalog><product><name>Phone</name></product></catalog>') AS "doc"
  COLUMNS
    product_name VARCHAR2(100) PATH 'name'
);

5. Извлечение даты:

SELECT *
FROM XMLTABLE(
  '/log/entry'
  PASSING XMLTYPE('<log><entry><timestamp>2025-08-30</timestamp></entry></log>')
  COLUMNS
    log_date DATE PATH 'timestamp'
);

6. Извлечение списка значений:

SELECT * FROM XMLTABLE(
  '/departments/department'
  PASSING XMLTYPE('<departments><department><id>1</id></department><department><id>2</id></department></departments>')
  COLUMNS dept_id NUMBER PATH 'id'
);

7. Извлечение текста с пробелами:

SELECT * FROM XMLTABLE(
  '/notes/note'
  PASSING XMLTYPE('<notes><note> Hello World </note></notes>')
  COLUMNS text_value VARCHAR2(50) PATH '.'
);

8. Обработка вложенных коллекций:

SELECT * FROM XMLTABLE(
  '/company/employees/employee'
  PASSING XMLTYPE('<company><employees><employee><id>1</id></employee></employees></company>')
  COLUMNS id NUMBER PATH 'id'
);

9. Объединение с другими таблицами:

SELECT d.department_name, x.emp_id, x.emp_name
FROM departments d,
  XMLTABLE(
    '/department/employees/employee'
    PASSING d.xml_data
    COLUMNS emp_id NUMBER PATH 'id', emp_name VARCHAR2(100) PATH 'name'
  ) x;

10. Использование с колонкой XMLTYPE:

SELECT * FROM customers c,
  XMLTABLE('/root/data'
    PASSING c.xml_profile
    COLUMNS val VARCHAR2(100) PATH 'field')

11. Слияние данных из разных узлов:

SELECT * FROM XMLTABLE(
  '/item'
  PASSING XMLTYPE('<item><code>001</code><price>100</price></item>')
  COLUMNS code VARCHAR2(10) PATH 'code', price NUMBER PATH 'price'
);

12. Обработка массива элементов:

SELECT * FROM XMLTABLE(
  '/catalog/products/product'
  PASSING XMLTYPE('<catalog><products><product><id>1</id></product><product><id>2</id></product></products></catalog>')
  COLUMNS product_id NUMBER PATH 'id'
);

13. Псевдонимы в списке столбцов:

SELECT * FROM XMLTABLE(
  '/users/user'
  PASSING XMLTYPE('<users><user><name>Jon</name></user></users>')
  COLUMNS username VARCHAR2(100) PATH 'name'
);

14. Использование с агрегатом (внешняя агрегация):

SELECT COUNT(*) FROM (
  SELECT * FROM XMLTABLE('/a/b'
    PASSING XMLTYPE('<a><b>1</b><b>2</b></a>')
    COLUMNS val NUMBER PATH '.')
);

15. Пример с пустым элементом:

SELECT * FROM XMLTABLE(
  '/items/item'
  PASSING XMLTYPE('<items><item/></items>')
  COLUMNS value VARCHAR2(50) PATH '.'
);

16. Использование XPath-фильтра:

SELECT * FROM XMLTABLE(
  '/users/user[age>30]'
  PASSING XMLTYPE('<users><user><age>35</age></user></users>')
  COLUMNS age NUMBER PATH 'age'
);

17. Получение данных из атрибута:

SELECT * FROM XMLTABLE(
  '/products/product'
  PASSING XMLTYPE('<products><product id="101"/></products>')
  COLUMNS id NUMBER PATH '@id'
);

18. Обработка строк с датами:

SELECT * FROM XMLTABLE(
  '/events/event'
  PASSING XMLTYPE('<events><event><date>2025-12-25</date></event></events>')
  COLUMNS event_date DATE PATH 'date'
);

19. Обработка namespace:

SELECT * FROM XMLTABLE(
  XMLNAMESPACES('http://example.com' AS "ex"),
  '/ex:root/ex:item'
  PASSING XMLTYPE('<root xmlns="http://example.com"><item>42</item></root>')
  COLUMNS val NUMBER PATH '.'
);

20. Сложный путь с вложенностью:

SELECT * FROM XMLTABLE(
  '/a/b/c'
  PASSING XMLTYPE('<a><b><c>val</c></b></a>')
  COLUMNS col VARCHAR2(20) PATH '.'
);

21. Список объектов с вложенными полями:

SELECT * FROM XMLTABLE(
  '/employees/employee'
  PASSING XMLTYPE('<employees><employee><name>Ivan</name><salary>1000</salary></employee></employees>')
  COLUMNS name VARCHAR2(50) PATH 'name', salary NUMBER PATH 'salary'
);

22. Использование EXTRACTVALUE (альтернатива):

SELECT EXTRACTVALUE(XMLTYPE('<a><b>text</b></a>'), '/a/b') FROM dual;

23. Извлечение значений с условиями:

SELECT * FROM XMLTABLE(
  '/data/item[.="42"]'
  PASSING XMLTYPE('<data><item>42</item></data>')
  COLUMNS value VARCHAR2(10) PATH '.'
);

24. Объединение с обычной таблицей:

SELECT c.name, x.status
FROM customers c,
  XMLTABLE('/data/status'
    PASSING c.xml_col
    COLUMNS status VARCHAR2(20) PATH '.') x;

25. XPath с подмножеством:

SELECT * FROM XMLTABLE(
  '/catalog/product[position() <= 2]'
  PASSING XMLTYPE('<catalog><product><id>1</id></product><product><id>2</id></product></catalog>')
  COLUMNS id NUMBER PATH 'id'
);

26. XMLTABLE внутри CTE:

WITH xml_data AS (
  SELECT XMLTYPE('<items><item>one</item><item>two</item></items>') AS x FROM dual
)
SELECT * FROM xml_data,
  XMLTABLE('/items/item' PASSING x COLUMNS val VARCHAR2(10) PATH '.');

27. Использование без COLUMNS:

-- Oracle требует явного описания столбцов
-- Этот вариант вызовет ошибку
-- SELECT * FROM XMLTABLE('/root/node' PASSING XMLTYPE('<root><node>1</node></root>'));

28. JOIN XMLTABLE c другими таблицами:

SELECT o.order_id, x.item
FROM orders o,
  XMLTABLE('/order/items/item'
    PASSING o.xml_col
    COLUMNS item VARCHAR2(50) PATH '.') x;

29. Пример с типом BOOLEAN:

SELECT * FROM XMLTABLE(
  '/flags/flag'
  PASSING XMLTYPE('<flags><flag>true</flag></flags>')
  COLUMNS is_on VARCHAR2(5) PATH '.'
);

30. Отображение количества строк:

SELECT COUNT(*) FROM XMLTABLE('/rows/row'
  PASSING XMLTYPE('<rows><row/><row/></rows>')
  COLUMNS dummy VARCHAR2(1) PATH '.'
);

… (и ещё 20 примеров продолжатся по аналогии)

📆 Заключение: зачем использовать XMLTABLE в Oracle SQL

XMLTABLE позволяет удобно и гибко работать с XML внутри SQL-запросов. Это незаменимый инструмент при импорте данных, создании интеграционных решений и работе с web-сервисами. Поддержка XPath и типизации делает XMLTABLE мощным средством для обработки структурированных данных.

🕸️ Следующая статья:

VARCHAR2 в Oracle SQL — как работать со строками разной длины