1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.50. xml2 — XPath querying and XSLT functionality</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="uuid-ossp.html" title="F.49. uuid-ossp — a UUID generator" /><link rel="next" href="contrib-prog.html" title="Appendix G. Additional Supplied Programs" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.50. xml2 — XPath querying and XSLT functionality</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="uuid-ossp.html" title="F.49. uuid-ossp — a UUID generator">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="contrib-prog.html" title="Appendix G. Additional Supplied Programs">Next</a></td></tr></table><hr /></div><div class="sect1" id="XML2"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.50. xml2 — XPath querying and XSLT functionality <a href="#XML2" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="xml2.html#XML2-DEPRECATION">F.50.1. Deprecation Notice</a></span></dt><dt><span class="sect2"><a href="xml2.html#XML2-FUNCTIONS">F.50.2. Description of Functions</a></span></dt><dt><span class="sect2"><a href="xml2.html#XML2-XPATH-TABLE">F.50.3. <code class="literal">xpath_table</code></a></span></dt><dt><span class="sect2"><a href="xml2.html#XML2-XSLT">F.50.4. XSLT Functions</a></span></dt><dt><span class="sect2"><a href="xml2.html#XML2-AUTHOR">F.50.5. Author</a></span></dt></dl></div><a id="id-1.11.7.60.2" class="indexterm"></a><p>
The <code class="filename">xml2</code> module provides XPath querying and
XSLT functionality.
</p><div class="sect2" id="XML2-DEPRECATION"><div class="titlepage"><div><div><h3 class="title">F.50.1. Deprecation Notice <a href="#XML2-DEPRECATION" class="id_link">#</a></h3></div></div></div><p>
From <span class="productname">PostgreSQL</span> 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 <code class="email"><<a class="email" href="mailto:pgsql-hackers@lists.postgresql.org">pgsql-hackers@lists.postgresql.org</a>></code> so that the deficiency
can be addressed.
</p></div><div class="sect2" id="XML2-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">F.50.2. Description of Functions <a href="#XML2-FUNCTIONS" class="id_link">#</a></h3></div></div></div><p>
<a class="xref" href="xml2.html#XML2-FUNCTIONS-TABLE" title="Table F.36. xml2 Functions">Table F.36</a> shows the functions provided by this module.
These functions provide straightforward XML parsing and XPath queries.
</p><div class="table" id="XML2-FUNCTIONS-TABLE"><p class="title"><strong>Table F.36. <code class="filename">xml2</code> Functions</strong></p><div class="table-contents"><table class="table" summary="xml2 Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xml_valid</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code> )
→ <code class="returnvalue">boolean</code>
</p>
<p>
Parses the given document and returns true if the
document is well-formed XML. (Note: this is an alias for the standard
PostgreSQL function <code class="function">xml_is_well_formed()</code>. The
name <code class="function">xml_valid()</code> is technically incorrect since validity
and well-formedness have different meanings in XML.)
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xpath_string</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code>, <em class="parameter"><code>query</code></em> <code class="type">text</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Evaluates the XPath query on the supplied document, and
casts the result to <code class="type">text</code>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xpath_number</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code>, <em class="parameter"><code>query</code></em> <code class="type">text</code> )
→ <code class="returnvalue">real</code>
</p>
<p>
Evaluates the XPath query on the supplied document, and
casts the result to <code class="type">real</code>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xpath_bool</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code>, <em class="parameter"><code>query</code></em> <code class="type">text</code> )
→ <code class="returnvalue">boolean</code>
</p>
<p>
Evaluates the XPath query on the supplied document, and
casts the result to <code class="type">boolean</code>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xpath_nodeset</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code>, <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>toptag</code></em> <code class="type">text</code>, <em class="parameter"><code>itemtag</code></em> <code class="type">text</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Evaluates the query on the document and wraps the result in XML
tags. If the result is multivalued, the output will look like:
</p><pre class="synopsis">
<toptag>
<itemtag>Value 1 which could be an XML fragment</itemtag>
<itemtag>Value 2....</itemtag>
</toptag>
</pre><p>
If either <em class="parameter"><code>toptag</code></em>
or <em class="parameter"><code>itemtag</code></em> is an empty string, the relevant tag
is omitted.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xpath_nodeset</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code>, <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>itemtag</code></em> <code class="type">text</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Like <code class="function">xpath_nodeset(document, query, toptag, itemtag)</code> but result omits <em class="parameter"><code>toptag</code></em>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xpath_nodeset</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code>, <em class="parameter"><code>query</code></em> <code class="type">text</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Like <code class="function">xpath_nodeset(document, query, toptag, itemtag)</code> but result omits both tags.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xpath_list</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code>, <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>separator</code></em> <code class="type">text</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
Evaluates the query on the document and returns multiple values
separated by the specified separator, for example <code class="literal">Value
1,Value 2,Value 3</code> if <em class="parameter"><code>separator</code></em>
is <code class="literal">,</code>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<code class="function">xpath_list</code> ( <em class="parameter"><code>document</code></em> <code class="type">text</code>, <em class="parameter"><code>query</code></em> <code class="type">text</code> )
→ <code class="returnvalue">text</code>
</p>
<p>
This is a wrapper for the above function that uses <code class="literal">,</code>
as the separator.
</p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="XML2-XPATH-TABLE"><div class="titlepage"><div><div><h3 class="title">F.50.3. <code class="literal">xpath_table</code> <a href="#XML2-XPATH-TABLE" class="id_link">#</a></h3></div></div></div><a id="id-1.11.7.60.6.2" class="indexterm"></a><pre class="synopsis">
xpath_table(text key, text document, text relation, text xpaths, text criteria) returns setof record
</pre><p>
<code class="function">xpath_table</code> 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 in
<a class="xref" href="xml2.html#XML2-XPATH-TABLE-PARAMETERS" title="Table F.37. xpath_table Parameters">Table F.37</a>.
</p><div class="table" id="XML2-XPATH-TABLE-PARAMETERS"><p class="title"><strong>Table F.37. <code class="function">xpath_table</code> Parameters</strong></p><div class="table-contents"><table class="table" summary="xpath_table Parameters" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td><em class="parameter"><code>key</code></em></td><td>
<p>
the name of the <span class="quote">“<span class="quote">key</span>”</span> field — this is just a field to be used as
the first column of the output table, i.e., it identifies the record from
which each output row came (see note below about multiple values)
</p>
</td></tr><tr><td><em class="parameter"><code>document</code></em></td><td>
<p>
the name of the field containing the XML document
</p>
</td></tr><tr><td><em class="parameter"><code>relation</code></em></td><td>
<p>
the name of the table or view containing the documents
</p>
</td></tr><tr><td><em class="parameter"><code>xpaths</code></em></td><td>
<p>
one or more XPath expressions, separated by <code class="literal">|</code>
</p>
</td></tr><tr><td><em class="parameter"><code>criteria</code></em></td><td>
<p>
the contents of the WHERE clause. This cannot be omitted, so use
<code class="literal">true</code> or <code class="literal">1=1</code> if you want to
process all the rows in the relation
</p>
</td></tr></tbody></table></div></div><br class="table-break" /><p>
These parameters (except the XPath strings) are just substituted
into a plain SQL SELECT statement, so you have some flexibility — the
statement is
</p><p>
<code class="literal">
SELECT <key>, <document> FROM <relation> WHERE <criteria>
</code>
</p><p>
so those parameters can be <span class="emphasis"><em>anything</em></span> 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.
</p><p>
The function has to be used in a <code class="literal">FROM</code> expression, with an
<code class="literal">AS</code> clause to specify the output columns; for example
</p><pre class="programlisting">
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);
</pre><p>
The <code class="literal">AS</code> clause defines the names and types of the columns in the
output table. The first is the <span class="quote">“<span class="quote">key</span>”</span> 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.
</p><p>
Notice that this example defines the <code class="structname">page_count</code> 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 the <code class="type">AS</code>
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 to
<code class="type">text</code> as the column type if you think your data has any problems.
</p><p>
The calling <code class="command">SELECT</code> statement doesn't necessarily have to be
just <code class="literal">SELECT *</code> — 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:
</p><pre class="programlisting">
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;
</pre><p>
as a more complicated example. Of course, you could wrap all
of this in a view for convenience.
</p><div class="sect3" id="XML2-XPATH-TABLE-MULTIVALUED-RESULTS"><div class="titlepage"><div><div><h4 class="title">F.50.3.1. Multivalued Results <a href="#XML2-XPATH-TABLE-MULTIVALUED-RESULTS" class="id_link">#</a></h4></div></div></div><p>
The <code class="function">xpath_table</code> function assumes that the results of each XPath query
might be multivalued, so the number of rows returned by the function
may not be the same as the number of input documents. The first row
returned contains the first result from each query, the second row the
second result from each query. If one of the queries has fewer values
than the others, null values will be returned instead.
</p><p>
In some cases, a user will know that a given XPath query will return
only a single result (perhaps a unique document identifier) — if used
alongside an XPath query returning multiple results, the single-valued
result will appear only on the first row of the result. The solution
to this is to use the key field as part of a join against a simpler
XPath query. As an example:
</p><pre class="programlisting">
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
</pre><p>
</p><p>
To get <code class="literal">doc_num</code> on every line, the solution is to use two invocations
of <code class="function">xpath_table</code> and join the results:
</p><pre class="programlisting">
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)
</pre><p>
</p></div></div><div class="sect2" id="XML2-XSLT"><div class="titlepage"><div><div><h3 class="title">F.50.4. XSLT Functions <a href="#XML2-XSLT" class="id_link">#</a></h3></div></div></div><p>
The following functions are available if libxslt is installed:
</p><div class="sect3" id="XML2-XSLT-XSLT-PROCESS"><div class="titlepage"><div><div><h4 class="title">F.50.4.1. <code class="literal">xslt_process</code> <a href="#XML2-XSLT-XSLT-PROCESS" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.60.7.3.2" class="indexterm"></a><pre class="synopsis">
xslt_process(text document, text stylesheet, text paramlist) returns text
</pre><p>
This function applies the XSL stylesheet to the document and returns
the transformed result. The <code class="literal">paramlist</code> is a list of parameter
assignments to be used in the transformation, specified in the form
<code class="literal">a=1,b=2</code>. Note that the
parameter parsing is very simple-minded: parameter values cannot
contain commas!
</p><p>
There is also a two-parameter version of <code class="function">xslt_process</code> which
does not pass any parameters to the transformation.
</p></div></div><div class="sect2" id="XML2-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.50.5. Author <a href="#XML2-AUTHOR" class="id_link">#</a></h3></div></div></div><p>
John Gray <code class="email"><<a class="email" href="mailto:jgray@azuli.co.uk">jgray@azuli.co.uk</a>></code>
</p><p>
Development of this module was sponsored by Torchbox Ltd. (www.torchbox.com).
It has the same BSD license as PostgreSQL.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="uuid-ossp.html" title="F.49. uuid-ossp — a UUID generator">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="contrib-prog.html" title="Appendix G. Additional Supplied Programs">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.49. uuid-ossp — a UUID generator </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Appendix G. Additional Supplied Programs</td></tr></table></div></body></html>
|
