金仓数据库KingbaseES XMLTABLE的使用介绍
关键字:
KingbaseES、XML、XMLTABLE、人大金仓、KingbaseES
一、功能概述
xmltable表达式基于给定的XML值产生一个表、一个抽取行的XPath过滤器以及一个列定义集,通俗的讲,就是将一个XML文档的元素、内容和属性以表格的形式展示出来。具体的语法如下:
xmltable( [XMLNAMESPACES(namespace uri AS namespace name[, ...]), ]row_expression PASSING [BY { REF | VALUE }] document_expression [BY
{ REF | VALUE }] COLUMNS name { type [PATH column_expression][DEFAULT default_expression] [NOT NULL | NULL] | FOR ORDINALITY }[, ...])
各个参数的含义如下:
- 可选的XMLNAMESPACES子句是一个逗号分隔的命名空间列表。它指定文档中使用的XML命名空间及其别名。当前不支持默认的名字空间说明。
- 必需的row_expression参数是一个XPath 1.0表达式,通过传递document_expression作为其上下文项,得到一组XML节点。这些节点就是xmltable转换为输出行的内容。如果document_expression为空,或者row_expression产生空节点集或节点集以外的任何值,则不会产生行。
- document_expression为row_expression项提供了上下文。它必须是一个格式良好的XML文档;不接受片段/森林。
- 必需的COLUMNS子句指定输出表中的列列表。列名和类型是必需的,(PATH)路径和默认值是可选的。路径后面对应每列将要映射的内容,此内容也可以是多个元素或属性拼接的内容。
- 被标记为FOR ORDINALITY的列将按照从row_expression的结果节点顺序,从1开始,填充行号。最多只能有一个列被标记为FOR ORDINALITY。
二、使用实例
首先,创建一个xmldata表格,里面的data列存放XML文档;然后,根据XPath定位的节点来确定转化为表形式的XML文档的范围;最后,根据COLUMNS子句描述将XML文档的元素、属性和内容映射到表格对应的列中。
CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
<ROW id="1">
<COUNTRY_ID>AU</COUNTRY_ID>
<COUNTRY_NAME>Australia</COUNTRY_NAME>
</ROW>
<ROW id="5">
<COUNTRY_ID>JP</COUNTRY_ID>
<COUNTRY_NAME>Japan</COUNTRY_NAME>
<PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
<SIZE unit="sq_mi">145935</SIZE>
</ROW>
<ROW id="6">
<COUNTRY_ID>SG</COUNTRY_ID>
<COUNTRY_NAME>Singapore</COUNTRY_NAME>
<SIZE unit="sq_km">697</SIZE>
</ROW>
</ROWS>
$$ AS data;
SELECT xmltable.*
FROM xmldata,
XMLTABLE('//ROWS/ROW' PASSING data
COLUMNS id int PATH '@id',
ordinality FOR ORDINALITY,
"COUNTRY_NAME" text,
country_id text PATH 'COUNTRY_ID',
size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
size_other text PATH 'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;
输出结果:
id | ordinality | country_name | country_id | size_sq_km | size_other | premier_name
----+------------+--------------+------------+------------+--------------+---------------
1 | 1 | Australia | AU | | | not specified
5 | 2 | Japan | JP | | 145935 sq_mi | Shinzo Abe
6 | 3 | Singapore | SG | 697 | | not specified
(3 行记录)