Movatterモバイル変換

F.48.1. Deprecation Notice

F.48.2. Description of Functions

F.48.3.xpath_table

F.48.4. XSLT Functions

F.48.5. Author

FromPostgreSQL 8.3 on, there is XML-related functionality based on the SQL/XML standard in the core server. That functionality covers XML syntax checking and XPath queries, which is what this module does, and more, but the API is not at all compatible. It is planned that this module will be removed in a future version of PostgreSQL in favor of the newer standard API, so you are encouraged to try converting your applications. If you find that some of the functionality of this module is not available in an adequate form with the newer API, please explain your issue to<pgsql-hackers@lists.postgresql.org> so that the deficiency can be addressed.

Table F.35 shows the functions provided by this module. These functions provide straightforward XML parsing and XPath queries.

<toptag><itemtag>Value 1 which could be an XML fragment</itemtag><itemtag>Value 2....</itemtag></toptag>

xpath_table(text key, text document, text relation, text xpaths, text criteria) returns setof record

xpath_table is a table function that evaluates a set of XPath queries on each of a set of documents and returns the results as a table. The primary key field from the original document table is returned as the first column of the result so that the result set can readily be used in joins. The parameters are described inTable F.36.

These parameters (except the XPath strings) are just substituted into a plain SQL SELECT statement, so you have some flexibility — the statement is

SELECT <key>, <document> FROM <relation> WHERE <criteria>

so those parameters can beanything valid in those particular locations. The result from this SELECT needs to return exactly two columns (which it will unless you try to list multiple fields for key or document). Beware that this simplistic approach requires that you validate any user-supplied values to avoid SQL injection attacks.

The function has to be used in aFROM expression, with anAS clause to specify the output columns; for example

SELECT * FROMxpath_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);

TheAS clause defines the names and types of the columns in the output table. The first is the“key” field and the rest correspond to the XPath queries. If there are more XPath queries than result columns, the extra queries will be ignored. If there are more result columns than XPath queries, the extra columns will be NULL.

Notice that this example defines thepage_count result column as an integer. The function deals internally with string representations, so when you say you want an integer in the output, it will take the string representation of the XPath result and use PostgreSQL input functions to transform it into an integer (or whatever type theAS clause requests). An error will result if it can't do this — for example if the result is empty — so you may wish to just stick totext as the column type if you think your data has any problems.

The callingSELECT statement doesn't necessarily have to be justSELECT * — it can reference the output columns by name or join them to other tables. The function produces a virtual table with which you can perform any operation you wish (e.g., aggregation, joining, sorting etc.). So we could also have:

SELECT t.title, p.fullname, p.emailFROM 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 pWHERE t.author_id = p.person_id;

as a more complicated example. Of course, you could wrap all of this in a view for convenience.

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

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=1ORDER 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)

The following functions are available if libxslt is installed:

xslt_process(text document, text stylesheet, text paramlist) returns text

John Gray<jgray@azuli.co.uk>

Development of this module was sponsored by Torchbox Ltd. (www.torchbox.com). It has the same BSD license as PostgreSQL.