| 1 |
<!-- doc/src/sgml/xml2.sgml --> |
| 2 |
|
| 3 |
<sect1 id="xml2" xreflabel="xml2"> |
| 4 |
<title>xml2 — XPath querying and XSLT functionality</title> |
| 5 |
|
| 6 |
<indexterm zone="xml2"> |
| 7 |
<primary>xml2</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<para> |
| 11 |
The <filename>xml2</filename> module provides XPath querying and |
| 12 |
XSLT functionality. |
| 13 |
</para> |
| 14 |
|
| 15 |
<sect2 id="xml2-deprecation"> |
| 16 |
<title>Deprecation Notice</title> |
| 17 |
|
| 18 |
<para> |
| 19 |
From <productname>PostgreSQL</productname> 8.3 on, there is XML-related |
| 20 |
functionality based on the SQL/XML standard in the core server. |
| 21 |
That functionality covers XML syntax checking and XPath queries, |
| 22 |
which is what this module does, and more, but the API is |
| 23 |
not at all compatible. It is planned that this module will be |
| 24 |
removed in a future version of PostgreSQL in favor of the newer standard API, so |
| 25 |
you are encouraged to try converting your applications. If you |
| 26 |
find that some of the functionality of this module is not |
| 27 |
available in an adequate form with the newer API, please explain |
| 28 |
your issue to <email>[email protected]</email> so that the deficiency |
| 29 |
can be addressed. |
| 30 |
</para> |
| 31 |
</sect2> |
| 32 |
|
| 33 |
<sect2 id="xml2-functions"> |
| 34 |
<title>Description of Functions</title> |
| 35 |
|
| 36 |
<para> |
| 37 |
<xref linkend="xml2-functions-table"/> shows the functions provided by this module. |
| 38 |
These functions provide straightforward XML parsing and XPath queries. |
| 39 |
</para> |
| 40 |
|
| 41 |
<table id="xml2-functions-table"> |
| 42 |
<title><filename>xml2</filename> Functions</title> |
| 43 |
<tgroup cols="1"> |
| 44 |
<thead> |
| 45 |
<row> |
| 46 |
<entry role="func_table_entry"><para role="func_signature"> |
| 47 |
Function |
| 48 |
</para> |
| 49 |
<para> |
| 50 |
Description |
| 51 |
</para></entry> |
| 52 |
</row> |
| 53 |
</thead> |
| 54 |
|
| 55 |
<tbody> |
| 56 |
<row> |
| 57 |
<entry role="func_table_entry"><para role="func_signature"> |
| 58 |
<function>xml_valid</function> ( <parameter>document</parameter> <type>text</type> ) |
| 59 |
<returnvalue>boolean</returnvalue> |
| 60 |
</para> |
| 61 |
<para> |
| 62 |
Parses the given document and returns true if the |
| 63 |
document is well-formed XML. (Note: this is an alias for the standard |
| 64 |
PostgreSQL function <function>xml_is_well_formed()</function>. The |
| 65 |
name <function>xml_valid()</function> is technically incorrect since validity |
| 66 |
and well-formedness have different meanings in XML.) |
| 67 |
</para></entry> |
| 68 |
</row> |
| 69 |
|
| 70 |
<row> |
| 71 |
<entry role="func_table_entry"><para role="func_signature"> |
| 72 |
<function>xpath_string</function> ( <parameter>document</parameter> <type>text</type>, <parameter>query</parameter> <type>text</type> ) |
| 73 |
<returnvalue>text</returnvalue> |
| 74 |
</para> |
| 75 |
<para> |
| 76 |
Evaluates the XPath query on the supplied document, and |
| 77 |
casts the result to <type>text</type>. |
| 78 |
</para></entry> |
| 79 |
</row> |
| 80 |
|
| 81 |
<row> |
| 82 |
<entry role="func_table_entry"><para role="func_signature"> |
| 83 |
<function>xpath_number</function> ( <parameter>document</parameter> <type>text</type>, <parameter>query</parameter> <type>text</type> ) |
| 84 |
<returnvalue>real</returnvalue> |
| 85 |
</para> |
| 86 |
<para> |
| 87 |
Evaluates the XPath query on the supplied document, and |
| 88 |
casts the result to <type>real</type>. |
| 89 |
</para></entry> |
| 90 |
</row> |
| 91 |
|
| 92 |
<row> |
| 93 |
<entry role="func_table_entry"><para role="func_signature"> |
| 94 |
<function>xpath_bool</function> ( <parameter>document</parameter> <type>text</type>, <parameter>query</parameter> <type>text</type> ) |
| 95 |
<returnvalue>boolean</returnvalue> |
| 96 |
</para> |
| 97 |
<para> |
| 98 |
Evaluates the XPath query on the supplied document, and |
| 99 |
casts the result to <type>boolean</type>. |
| 100 |
</para></entry> |
| 101 |
</row> |
| 102 |
|
| 103 |
<row> |
| 104 |
<entry role="func_table_entry"><para role="func_signature"> |
| 105 |
<function>xpath_nodeset</function> ( <parameter>document</parameter> <type>text</type>, <parameter>query</parameter> <type>text</type>, <parameter>toptag</parameter> <type>text</type>, <parameter>itemtag</parameter> <type>text</type> ) |
| 106 |
<returnvalue>text</returnvalue> |
| 107 |
</para> |
| 108 |
<para> |
| 109 |
Evaluates the query on the document and wraps the result in XML |
| 110 |
tags. If the result is multivalued, the output will look like: |
| 111 |
<synopsis> |
| 112 |
<toptag> |
| 113 |
<itemtag>Value 1 which could be an XML fragment</itemtag> |
| 114 |
<itemtag>Value 2....</itemtag> |
| 115 |
</toptag> |
| 116 |
</synopsis> |
| 117 |
If either <parameter>toptag</parameter> |
| 118 |
or <parameter>itemtag</parameter> is an empty string, the relevant tag |
| 119 |
is omitted. |
| 120 |
</para></entry> |
| 121 |
</row> |
| 122 |
|
| 123 |
<row> |
| 124 |
<entry role="func_table_entry"><para role="func_signature"> |
| 125 |
<function>xpath_nodeset</function> ( <parameter>document</parameter> <type>text</type>, <parameter>query</parameter> <type>text</type>, <parameter>itemtag</parameter> <type>text</type> ) |
| 126 |
<returnvalue>text</returnvalue> |
| 127 |
</para> |
| 128 |
<para> |
| 129 |
Like <function>xpath_nodeset(document, query, toptag, itemtag)</function> but result omits <parameter>toptag</parameter>. |
| 130 |
</para></entry> |
| 131 |
</row> |
| 132 |
|
| 133 |
<row> |
| 134 |
<entry role="func_table_entry"><para role="func_signature"> |
| 135 |
<function>xpath_nodeset</function> ( <parameter>document</parameter> <type>text</type>, <parameter>query</parameter> <type>text</type> ) |
| 136 |
<returnvalue>text</returnvalue> |
| 137 |
</para> |
| 138 |
<para> |
| 139 |
Like <function>xpath_nodeset(document, query, toptag, itemtag)</function> but result omits both tags. |
| 140 |
</para></entry> |
| 141 |
</row> |
| 142 |
|
| 143 |
<row> |
| 144 |
<entry role="func_table_entry"><para role="func_signature"> |
| 145 |
<function>xpath_list</function> ( <parameter>document</parameter> <type>text</type>, <parameter>query</parameter> <type>text</type>, <parameter>separator</parameter> <type>text</type> ) |
| 146 |
<returnvalue>text</returnvalue> |
| 147 |
</para> |
| 148 |
<para> |
| 149 |
Evaluates the query on the document and returns multiple values |
| 150 |
separated by the specified separator, for example <literal>Value |
| 151 |
1,Value 2,Value 3</literal> if <parameter>separator</parameter> |
| 152 |
is <literal>,</literal>. |
| 153 |
</para></entry> |
| 154 |
</row> |
| 155 |
|
| 156 |
<row> |
| 157 |
<entry role="func_table_entry"><para role="func_signature"> |
| 158 |
<function>xpath_list</function> ( <parameter>document</parameter> <type>text</type>, <parameter>query</parameter> <type>text</type> ) |
| 159 |
<returnvalue>text</returnvalue> |
| 160 |
</para> |
| 161 |
<para> |
| 162 |
This is a wrapper for the above function that uses <literal>,</literal> |
| 163 |
as the separator. |
| 164 |
</para></entry> |
| 165 |
</row> |
| 166 |
</tbody> |
| 167 |
</tgroup> |
| 168 |
</table> |
| 169 |
</sect2> |
| 170 |
|
| 171 |
<sect2 id="xml2-xpath-table"> |
| 172 |
<title><literal>xpath_table</literal></title> |
| 173 |
|
| 174 |
<indexterm> |
| 175 |
<primary>xpath_table</primary> |
| 176 |
</indexterm> |
| 177 |
|
| 178 |
<synopsis> |
| 179 |
xpath_table(text key, text document, text relation, text xpaths, text criteria) returns setof record |
| 180 |
</synopsis> |
| 181 |
|
| 182 |
<para> |
| 183 |
<function>xpath_table</function> is a table function that evaluates a set of XPath |
| 184 |
queries on each of a set of documents and returns the results as a |
| 185 |
table. The primary key field from the original document table is returned |
| 186 |
as the first column of the result so that the result set |
| 187 |
can readily be used in joins. The parameters are described in |
| 188 |
<xref linkend="xml2-xpath-table-parameters"/>. |
| 189 |
</para> |
| 190 |
|
| 191 |
<table id="xml2-xpath-table-parameters"> |
| 192 |
<title><function>xpath_table</function> Parameters</title> |
| 193 |
<tgroup cols="2"> |
| 194 |
<colspec colname="col1" colwidth="1*"/> |
| 195 |
<colspec colname="col2" colwidth="2*"/> |
| 196 |
<thead> |
| 197 |
<row> |
| 198 |
<entry>Parameter</entry> |
| 199 |
<entry>Description</entry> |
| 200 |
</row> |
| 201 |
</thead> |
| 202 |
<tbody> |
| 203 |
<row> |
| 204 |
<entry><parameter>key</parameter></entry> |
| 205 |
<entry> |
| 206 |
<para> |
| 207 |
the name of the <quote>key</quote> field — this is just a field to be used as |
| 208 |
the first column of the output table, i.e., it identifies the record from |
| 209 |
which each output row came (see note below about multiple values) |
| 210 |
</para> |
| 211 |
</entry> |
| 212 |
</row> |
| 213 |
<row> |
| 214 |
<entry><parameter>document</parameter></entry> |
| 215 |
<entry> |
| 216 |
<para> |
| 217 |
the name of the field containing the XML document |
| 218 |
</para> |
| 219 |
</entry> |
| 220 |
</row> |
| 221 |
<row> |
| 222 |
<entry><parameter>relation</parameter></entry> |
| 223 |
<entry> |
| 224 |
<para> |
| 225 |
the name of the table or view containing the documents |
| 226 |
</para> |
| 227 |
</entry> |
| 228 |
</row> |
| 229 |
<row> |
| 230 |
<entry><parameter>xpaths</parameter></entry> |
| 231 |
<entry> |
| 232 |
<para> |
| 233 |
one or more XPath expressions, separated by <literal>|</literal> |
| 234 |
</para> |
| 235 |
</entry> |
| 236 |
</row> |
| 237 |
<row> |
| 238 |
<entry><parameter>criteria</parameter></entry> |
| 239 |
<entry> |
| 240 |
<para> |
| 241 |
the contents of the WHERE clause. This cannot be omitted, so use |
| 242 |
<literal>true</literal> or <literal>1=1</literal> if you want to |
| 243 |
process all the rows in the relation |
| 244 |
</para> |
| 245 |
</entry> |
| 246 |
</row> |
| 247 |
</tbody> |
| 248 |
</tgroup> |
| 249 |
</table> |
| 250 |
|
| 251 |
<para> |
| 252 |
These parameters (except the XPath strings) are just substituted |
| 253 |
into a plain SQL SELECT statement, so you have some flexibility — the |
| 254 |
statement is |
| 255 |
</para> |
| 256 |
|
| 257 |
<para> |
| 258 |
<literal> |
| 259 |
SELECT <key>, <document> FROM <relation> WHERE <criteria> |
| 260 |
</literal> |
| 261 |
</para> |
| 262 |
|
| 263 |
<para> |
| 264 |
so those parameters can be <emphasis>anything</emphasis> valid in those particular |
| 265 |
locations. The result from this SELECT needs to return exactly two |
| 266 |
columns (which it will unless you try to list multiple fields for key |
| 267 |
or document). Beware that this simplistic approach requires that you |
| 268 |
validate any user-supplied values to avoid SQL injection attacks. |
| 269 |
</para> |
| 270 |
|
| 271 |
<para> |
| 272 |
The function has to be used in a <literal>FROM</literal> expression, with an |
| 273 |
<literal>AS</literal> clause to specify the output columns; for example |
| 274 |
<programlisting> |
| 275 |
SELECT * FROM |
| 276 |
xpath_table('article_id', |
| 277 |
'article_xml', |
| 278 |
'articles', |
| 279 |
'/article/author|/article/pages|/article/title', |
| 280 |
'date_entered > ''2003-01-01'' ') |
| 281 |
AS t(article_id integer, author text, page_count integer, title text); |
| 282 |
</programlisting> |
| 283 |
The <literal>AS</literal> clause defines the names and types of the columns in the |
| 284 |
output table. The first is the <quote>key</quote> field and the rest correspond |
| 285 |
to the XPath queries. |
| 286 |
If there are more XPath queries than result columns, |
| 287 |
the extra queries will be ignored. If there are more result columns |
| 288 |
than XPath queries, the extra columns will be NULL. |
| 289 |
</para> |
| 290 |
|
| 291 |
<para> |
| 292 |
Notice that this example defines the <structname>page_count</structname> result |
| 293 |
column as an integer. The function deals internally with string |
| 294 |
representations, so when you say you want an integer in the output, it will |
| 295 |
take the string representation of the XPath result and use PostgreSQL input |
| 296 |
functions to transform it into an integer (or whatever type the <type>AS</type> |
| 297 |
clause requests). An error will result if it can't do this — for |
| 298 |
example if the result is empty — so you may wish to just stick to |
| 299 |
<type>text</type> as the column type if you think your data has any problems. |
| 300 |
</para> |
| 301 |
|
| 302 |
<para> |
| 303 |
The calling <command>SELECT</command> statement doesn't necessarily have to be |
| 304 |
just <literal>SELECT *</literal> — it can reference the output |
| 305 |
columns by name or join them to other tables. The function produces a |
| 306 |
virtual table with which you can perform any operation you wish (e.g., |
| 307 |
aggregation, joining, sorting etc.). So we could also have: |
| 308 |
<programlisting> |
| 309 |
SELECT t.title, p.fullname, p.email |
| 310 |
FROM xpath_table('article_id', 'article_xml', 'articles', |
| 311 |
'/article/title|/article/author/@id', |
| 312 |
'xpath_string(article_xml,''/article/@date'') > ''2003-03-20'' ') |
| 313 |
AS t(article_id integer, title text, author_id integer), |
| 314 |
tblPeopleInfo AS p |
| 315 |
WHERE t.author_id = p.person_id; |
| 316 |
</programlisting> |
| 317 |
as a more complicated example. Of course, you could wrap all |
| 318 |
of this in a view for convenience. |
| 319 |
</para> |
| 320 |
|
| 321 |
<sect3 id="xml2-xpath-table-multivalued-results"> |
| 322 |
<title>Multivalued Results</title> |
| 323 |
|
| 324 |
<para> |
| 325 |
The <function>xpath_table</function> function assumes that the results of each XPath query |
| 326 |
might be multivalued, so the number of rows returned by the function |
| 327 |
may not be the same as the number of input documents. The first row |
| 328 |
returned contains the first result from each query, the second row the |
| 329 |
second result from each query. If one of the queries has fewer values |
| 330 |
than the others, null values will be returned instead. |
| 331 |
</para> |
| 332 |
|
| 333 |
<para> |
| 334 |
In some cases, a user will know that a given XPath query will return |
| 335 |
only a single result (perhaps a unique document identifier) — if used |
| 336 |
alongside an XPath query returning multiple results, the single-valued |
| 337 |
result will appear only on the first row of the result. The solution |
| 338 |
to this is to use the key field as part of a join against a simpler |
| 339 |
XPath query. As an example: |
| 340 |
|
| 341 |
<programlisting> |
| 342 |
CREATE TABLE test ( |
| 343 |
id int PRIMARY KEY, |
| 344 |
xml text |
| 345 |
); |
| 346 |
|
| 347 |
INSERT INTO test VALUES (1, '<doc num="C1"> |
| 348 |
<line num="L1"><a>1</a><b>2</b><c>3</c></line> |
| 349 |
<line num="L2"><a>11</a><b>22</b><c>33</c></line> |
| 350 |
</doc>'); |
| 351 |
|
| 352 |
INSERT INTO test VALUES (2, '<doc num="C2"> |
| 353 |
<line num="L1"><a>111</a><b>222</b><c>333</c></line> |
| 354 |
<line num="L2"><a>111</a><b>222</b><c>333</c></line> |
| 355 |
</doc>'); |
| 356 |
|
| 357 |
SELECT * FROM |
| 358 |
xpath_table('id','xml','test', |
| 359 |
'/doc/@num|/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c', |
| 360 |
'true') |
| 361 |
AS t(id int, doc_num varchar(10), line_num varchar(10), val1 int, val2 int, val3 int) |
| 362 |
WHERE id = 1 ORDER BY doc_num, line_num |
| 363 |
|
| 364 |
id | doc_num | line_num | val1 | val2 | val3 |
| 365 |
----+---------+----------+------+------+------ |
| 366 |
1 | C1 | L1 | 1 | 2 | 3 |
| 367 |
1 | | L2 | 11 | 22 | 33 |
| 368 |
</programlisting> |
| 369 |
</para> |
| 370 |
|
| 371 |
<para> |
| 372 |
To get <literal>doc_num</literal> on every line, the solution is to use two invocations |
| 373 |
of <function>xpath_table</function> and join the results: |
| 374 |
|
| 375 |
<programlisting> |
| 376 |
SELECT t.*,i.doc_num FROM |
| 377 |
xpath_table('id', 'xml', 'test', |
| 378 |
'/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c', |
| 379 |
'true') |
| 380 |
AS t(id int, line_num varchar(10), val1 int, val2 int, val3 int), |
| 381 |
xpath_table('id', 'xml', 'test', '/doc/@num', 'true') |
| 382 |
AS i(id int, doc_num varchar(10)) |
| 383 |
WHERE i.id=t.id AND i.id=1 |
| 384 |
ORDER BY doc_num, line_num; |
| 385 |
|
| 386 |
id | line_num | val1 | val2 | val3 | doc_num |
| 387 |
----+----------+------+------+------+--------- |
| 388 |
1 | L1 | 1 | 2 | 3 | C1 |
| 389 |
1 | L2 | 11 | 22 | 33 | C1 |
| 390 |
(2 rows) |
| 391 |
</programlisting> |
| 392 |
</para> |
| 393 |
</sect3> |
| 394 |
</sect2> |
| 395 |
|
| 396 |
<sect2 id="xml2-xslt"> |
| 397 |
<title>XSLT Functions</title> |
| 398 |
|
| 399 |
<para> |
| 400 |
The following functions are available if libxslt is installed: |
| 401 |
</para> |
| 402 |
|
| 403 |
<sect3 id="xml2-xslt-xslt-process"> |
| 404 |
<title><literal>xslt_process</literal></title> |
| 405 |
|
| 406 |
<indexterm> |
| 407 |
<primary>xslt_process</primary> |
| 408 |
</indexterm> |
| 409 |
|
| 410 |
<synopsis> |
| 411 |
xslt_process(text document, text stylesheet, text paramlist) returns text |
| 412 |
</synopsis> |
| 413 |
|
| 414 |
<para> |
| 415 |
This function applies the XSL stylesheet to the document and returns |
| 416 |
the transformed result. The <literal>paramlist</literal> is a list of parameter |
| 417 |
assignments to be used in the transformation, specified in the form |
| 418 |
<literal>a=1,b=2</literal>. Note that the |
| 419 |
parameter parsing is very simple-minded: parameter values cannot |
| 420 |
contain commas! |
| 421 |
</para> |
| 422 |
|
| 423 |
<para> |
| 424 |
There is also a two-parameter version of <function>xslt_process</function> which |
| 425 |
does not pass any parameters to the transformation. |
| 426 |
</para> |
| 427 |
</sect3> |
| 428 |
</sect2> |
| 429 |
|
| 430 |
<sect2 id="xml2-author"> |
| 431 |
<title>Author</title> |
| 432 |
|
| 433 |
<para> |
| 434 |
John Gray <email>[email protected]</email> |
| 435 |
</para> |
| 436 |
|
| 437 |
<para> |
| 438 |
Development of this module was sponsored by Torchbox Ltd. (www.torchbox.com). |
| 439 |
It has the same BSD license as PostgreSQL. |
| 440 |
</para> |
| 441 |
</sect2> |
| 442 |
|
| 443 |
</sect1> |