xpath_table(text key, text document, text relation, text xpaths, text criteria) returns setof record
xpath_table是一个表函数,它在一组文档中的每一个上计算一组XPath查询,并且将结果作为一个表返回。来自原始文档表的主键字段被返回为结果的第一列,这样结果集可以被用于连接。其参数如下表所示。
表?21.2.?xpath_table参数
参数 | 描述 |
---|---|
key | 表的键字段名,用作输出第一列,描述输出结果来源。 |
document | 表中xml字段名,该字段存储的是xml格式的文本。 |
relation | 包含文档的表或视图的名称。 |
xpaths | xpath表达式,多个表达式用 | 隔开,指定xml文档域。 |
criteria | WHERE子句的内容。这不能被忽略,因此如果想要处理关系中的所有行,可以使用true或1=1。 |
这些参数(除了XPath字符串)只是会被替换到一个纯粹的SQL SELECT语句中,因此应该有一些灵活性—该语句是:
SELECT <key>, <document> FROM <relation> WHERE <criteria>
因此那些参数可以是那些特定位置上合法的任何值。SELECT的结果需要返回正好两列(除非尝试为键或文档列出多个域)。注意这种简单方法要求验证任何用户提供的值,避免SQL注入攻击。
示例
加载xml2扩展插件。
create extension xml2;
建立测试表,包含id和documents两个字段,其中documents字段类型为xml。
CREATE TABLE xmldata (id bigserial not null, documents xml); insert into xmldata values(1, '<test color="red">Red</test>'); insert into xmldata values(2, '<test color="green">Green</test>');
uxdb=#
select * from xmldata;
id | documents ----+----------------------------------- 1 | <test color="red">Red</test> 2 | <test color="green">Green</test> (2 rows)
通过xpath_table函数从表中检索关键字。
uxdb=#
SELECT * FROM xpath_table('id','documents', 'xmldata', '/test', 'true') AS t(doc_id integer, data text);
doc_id | data --------+---------- 1 | Red 2 | Green
该函数必须被使用在一个FROM表达式中,并带有一个AS子句来指定输出列,例如:
SELECT * FROM xpath_table('article_id', 'article_xml', 'articles', '/article/author|/article/pages|/article/title', 'date_entered > ''2003-01-01'' ') AS t(article_id integer, author text, page_count integer, title text);
AS子句定义了输出表中列的名称和类型。第一个是“key”域并且剩下的对应于XPath查询。如果XPath查询比结果列多,额外的查询将被忽略。如果结果列比XPath查询多,额外的列将是NULL。
这个示例定义page_count结果列为一个整数。该函数在内部处理字符串表达,因此在输出中想要一个整数时,它将采用XPath结果的字符串表达并且使用UXDB输入函数来将其转换成一个整数(或者AS子句要求的任何类型)。如果它无法做到这一点将会导致一个错误—例如结果是空—因此如果数据有任何问题,可能希望坚持用text作为列类型。
调用的SELECT语句不必只是SELECT * — 它可以用名称引用输出列或者将它们连接到其他表。该函数会产生一个虚拟表,可以在其上执行任何所需的操作(例如聚集、连接、排序等)。因此更复杂的示例如下:
SELECT t.title, p.fullname, p.email FROM xpath_table('article_id', 'article_xml', 'articles', '/article/title|/article/author/@id', 'xpath_string(article_xml,''/article/@date'') > ''2003-03-20'' ') AS t(article_id integer, title text, author_id integer), tblPeopleInfo AS p WHERE t.author_id = p.person_id;
当然,为了便利也可以把所有这些封装在一个视图中。
xpath_table函数假定每一个XPath 查询的结果可能是多值的,因此该函数返回的行数可能与输入文档的数目不同。被返回的第一行包含来自每一个查询的第一个结果,第二行则是来自每一个查询的第二个结果。如果其中一个查询的值比其他查询少,则会为它返回空值。
在某些情况下,一个用户将知道一个给定的XPath 查询将只返回一个单一结果(可能是一个唯一文档标识符) — 如果和一个返回多值的XPath 查询一起使用,单值结果将只出现在结果的第一行中。对于这种情况的解决方案是使用键域作为针对一个更简单 XPath查询的连接的一部分。一个示例:
CREATE TABLE test ( id int PRIMARY KEY, xml text ); INSERT INTO test VALUES (1, '<doc num="C1"> <line num="L1"><a>1</a><b>2</b><c>3</c></line> <line num="L2"><a>11</a><b>22</b><c>33</c></line> </doc>'); INSERT INTO test VALUES (2, '<doc num="C2"> <line num="L1"><a>111</a><b>222</b><c>333</c></line> <line num="L2"><a>111</a><b>222</b><c>333</c></line> </doc>'); SELECT * FROM xpath_table('id','xml','test', '/doc/@num|/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c', 'true') AS t(id int, doc_num varchar(10), line_num varchar(10), val1 int, val2 int, val3 int) WHERE id = 1 ORDER BY doc_num, line_num;
id | doc_num | line_num | val1 | val2 | val3 ----+---------+----------+------+------+------ 1 | C1 | L1 | 1 | 2 | 3 1 | | L2 | 11 | 22 | 33
要在每一行上得到doc_num,解决方案是使用xpath_table的两个调用并且连接结果:
SELECT t.*,i.doc_num FROM xpath_table('id', 'xml', 'test', '/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c', 'true') AS t(id int, line_num varchar(10), val1 int, val2 int, val3 int), xpath_table('id', 'xml', 'test', '/doc/@num', 'true') AS i(id int, doc_num varchar(10)) WHERE i.id=t.id AND i.id=1 ORDER BY doc_num, line_num;
id | line_num | val1 | val2 | val3 | doc_num ----+----------+------+------+------+--------- 1 | L1 | 1 | 2 | 3 | C1 1 | L2 | 11 | 22 | 33 | C1 (2 rows)