XMLType 测试记录

-- 创建表
CREATE TABLE employee_xml (
    emp_id NUMBER PRIMARY KEY,
    emp_data XMLType
);

-- 插入数据
INSERT INTO employee_xml VALUES (1, XMLType(
'<?xml version="1.0" encoding="UTF-8"?>
<employee>
    <personal_info>
        <name>李四</name>
        <age>30</age>
        <email>lisi@company.com</email>
    </personal_info>
    <work_info>
        <department>研发部</department>
        <position>高级工程师</position>
        <salary>80000</salary>
        <hire_date>2020-01-15</hire_date>
    </work_info>
    <skills>
        <skill>Java</skill>
        <skill>Oracle</skill>
        <skill>Python</skill>
    </skills>
</employee>'));

-- 查询员工信息
SQL> SELECT 
  2      e.emp_id,
  3      x.name,
  4      x.department,
  5      x.salary
  6  FROM employee_xml e,
  7      XMLTable('/employee'
  8          PASSING e.emp_data
  9          COLUMNS 
 10              name VARCHAR2(50) PATH 'personal_info/name',
 11              department VARCHAR2(50) PATH 'work_info/department',
 12              salary NUMBER PATH 'work_info/salary'
 13      ) x;

    EMP_ID NAME                                               DEPARTMENT                                             SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 李四                                             研发部                                               80000

set linesize 300
col name for a10
col department for a30
col salary for a40
SELECT 
    emp_id,
    EXTRACTVALUE(emp_data, '/employee/personal_info/name') as name,
    EXTRACTVALUE(emp_data, '/employee/work_info/department') as department,
    EXTRACTVALUE(emp_data, '/employee/work_info/salary') as salary
FROM employee_xml;



SQL> set linesize 300
SQL> col name for a10
SQL> col department for a30
SQL> col salary for a40
SQL> SELECT 
  2      emp_id,
  3      EXTRACTVALUE(emp_data, '/employee/personal_info/name') as name,
  4      EXTRACTVALUE(emp_data, '/employee/work_info/department') as department,
  5      EXTRACTVALUE(emp_data, '/employee/work_info/salary') as salary
  6  FROM employee_xml;

    EMP_ID NAME       DEPARTMENT                     SALARY
---------- ---------- ------------------------------ ----------------------------------------
         1 ??????     ?????????                      80000



-- 使用XMLTable查询技能列表
SELECT 
    e.emp_id,
    s.skill_name
FROM employee_xml e,
XMLTable('/employee/skills/skill'
    PASSING e.emp_data
    COLUMNS skill_name VARCHAR2(50) PATH '.'
) s;


SQL> SELECT 
  2      e.emp_id,
  3      s.skill_name
  4  FROM employee_xml e,
  5  XMLTable('/employee/skills/skill'
  6      PASSING e.emp_data
  7      COLUMNS skill_name VARCHAR2(50) PATH '.'
  8  ) s;

    EMP_ID SKILL_NAME
---------- --------------------------------------------------
         1 Java
         1 Oracle
         1 Python

SQL>