diff options
Diffstat (limited to 'doc/src/sgml/xml2.sgml')
| -rw-r--r-- | doc/src/sgml/xml2.sgml | 185 |
1 files changed, 87 insertions, 98 deletions
diff --git a/doc/src/sgml/xml2.sgml b/doc/src/sgml/xml2.sgml index 9943b579ab3..8639921997c 100644 --- a/doc/src/sgml/xml2.sgml +++ b/doc/src/sgml/xml2.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/xml2.sgml,v 1.8 2010/07/27 19:01:16 petere Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/xml2.sgml,v 1.9 2010/07/29 19:34:40 petere Exp $ --> <sect1 id="xml2"> <title>xml2</title> @@ -45,9 +45,9 @@ <tbody> <row> <entry> - <synopsis> - xml_is_well_formed(document) returns bool - </synopsis> +<synopsis> +xml_is_well_formed(document) returns bool +</synopsis> </entry> <entry> <para> @@ -61,11 +61,11 @@ </row> <row> <entry> - <synopsis> - xpath_string(document,query) returns text - xpath_number(document,query) returns float4 - xpath_bool(document,query) returns bool - </synopsis> +<synopsis> +xpath_string(document, query) returns text +xpath_number(document, query) returns float4 +xpath_bool(document, query) returns bool +</synopsis> </entry> <entry> <para> @@ -76,31 +76,29 @@ </row> <row> <entry> - <synopsis> - xpath_nodeset(document,query,toptag,itemtag) returns text - </synopsis> +<synopsis> +xpath_nodeset(document, query, toptag, itemtag) returns text +</synopsis> </entry> <entry> <para> This evaluates query on document and wraps the result in XML tags. If the result is multivalued, the output will look like: - </para> - <literal> - <toptag> - <itemtag>Value 1 which could be an XML fragment</itemtag> - <itemtag>Value 2....</itemtag> - </toptag> - </literal> - <para> +<synopsis> +<toptag> +<itemtag>Value 1 which could be an XML fragment</itemtag> +<itemtag>Value 2....</itemtag> +</toptag> +</synopsis> If either toptag or itemtag is an empty string, the relevant tag is omitted. </para> </entry> </row> <row> <entry> - <synopsis> - xpath_nodeset(document,query) returns text - </synopsis> +<synopsis> +xpath_nodeset(document, query) returns text +</synopsis> </entry> <entry> <para> @@ -110,9 +108,9 @@ </row> <row> <entry> - <synopsis> - xpath_nodeset(document,query,itemtag) returns text - </synopsis> +<synopsis> +xpath_nodeset(document, query, itemtag) returns text +</synopsis> </entry> <entry> <para> @@ -122,9 +120,9 @@ </row> <row> <entry> - <synopsis> - xpath_list(document,query,separator) returns text - </synopsis> +<synopsis> +xpath_list(document, query, separator) returns text +</synopsis> </entry> <entry> <para> @@ -136,9 +134,9 @@ </row> <row> <entry> - <synopsis> - xpath_list(document,query) returns text - </synopsis> +<synopsis> +xpath_list(document, query) returns text +</synopsis> </entry> <entry> This is a wrapper for the above function that uses <literal>,</> @@ -153,9 +151,9 @@ <sect2> <title><literal>xpath_table</literal></title> - <synopsis> - xpath_table(text key, text document, text relation, text xpaths, text criteria) returns setof record - </synopsis> +<synopsis> +xpath_table(text key, text document, text relation, text xpaths, text criteria) returns setof record +</synopsis> <para> <function>xpath_table</> is a table function that evaluates a set of XPath @@ -240,9 +238,7 @@ <para> The function has to be used in a <literal>FROM</> expression, with an <literal>AS</> clause to specify the output columns; for example - </para> - - <programlisting> +<programlisting> SELECT * FROM xpath_table('article_id', 'article_xml', @@ -250,9 +246,7 @@ xpath_table('article_id', '/article/author|/article/pages|/article/title', 'date_entered > ''2003-01-01'' ') AS t(article_id integer, author text, page_count integer, title text); - </programlisting> - - <para> +</programlisting> The <literal>AS</> clause defines the names and types of the columns in the output table. The first is the <quote>key</> field and the rest correspond to the XPath queries. @@ -278,9 +272,7 @@ AS t(article_id integer, author text, page_count integer, title text); 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: - </para> - - <programlisting> +<programlisting> SELECT t.title, p.fullname, p.email FROM xpath_table('article_id', 'article_xml', 'articles', '/article/title|/article/author/@id', @@ -288,9 +280,7 @@ FROM xpath_table('article_id', 'article_xml', 'articles', AS t(article_id integer, title text, author_id integer), tblPeopleInfo AS p WHERE t.author_id = p.person_id; - </programlisting> - - <para> +</programlisting> as a more complicated example. Of course, you could wrap all of this in a view for convenience. </para> @@ -314,60 +304,59 @@ WHERE t.author_id = p.person_id; 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: - </para> - <programlisting> - CREATE TABLE test ( - id int4 NOT NULL, - xml text, - CONSTRAINT pk PRIMARY KEY (id) - ); - - 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 int4, doc_num varchar(10), line_num varchar(10), val1 int4, val2 int4, val3 int4) - 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 - </programlisting> +<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 +</programlisting> + </para> <para> To get doc_num on every line, the solution is to use two invocations of xpath_table and join the results: - </para> - <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 int4, line_num varchar(10), val1 int4, val2 int4, val3 int4), - xpath_table('id', 'xml', 'test', '/doc/@num', 'true') - AS i(id int4, 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) - </programlisting> +<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) +</programlisting> + </para> </sect3> </sect2> @@ -381,9 +370,9 @@ WHERE t.author_id = p.person_id; <sect3> <title><literal>xslt_process</literal></title> - <synopsis> - xslt_process(text document, text stylesheet, text paramlist) returns text - </synopsis> +<synopsis> +xslt_process(text document, text stylesheet, text paramlist) returns text +</synopsis> <para> This function applies the XSL stylesheet to the document and returns |