Thread: scary xpath_table behaviour

When using pipe separated xpath statements to fill multiple columns of
output,  there is no guarantee of correlation among the columns of the
rows returned.

Using locally built 9.0.3 (--with-libxml)

I'm dealing with an element which has one fixed attribute (@page-layout)
and either (@collection_id and @type) or (@default).

I was hoping for a result set along the lines of

+---+--------+---------------+------+---------+
|id | layout | collection_id | type | default |
+---+--------|---------------+------+---------+
|1  |layout1 | collection1   | t1   | null    |
|2  |layout1 | collection1   | t3   | null    |
|3  |layout2 | null          | null | true    |
+---+--------+---------------+------+---------+

but instead I get

+---+--------+---------------+------+---------+
|id | layout | collection_id | type | default |
+---+--------|---------------+------+---------+
|1  |layout1 | collection1   | t1   | true    |
|2  |layout1 | collection1   | t3   | null    |
|1  |layout2 | null          | null | null    |
+---+--------+---------------+------+---------+

where all the non-null values from the last xpath are at the top of the
result set (as they are the only values returned for that xpath query).

Here's my actual
select x.* from
         xpath_table(
                       'doc_id',
                       'xml_text',
                       'static_docs',
                       '//*[name(.) = "page-layout"]/@name
                            | //*[name(.) = "page-layout"]/@collection-id
                            | //*[name(.) = "page-layout"]/@type
                            |  //*[name(.) = "page-layout"]/@default ',
                       'doc_id = ''lookups.xml'''
                      )
                      as x(doc_id text, chapter_layout text,
collection_id text, doc_type text, defaulted_type text)


NOTE:  There is a multiplicity of namespaces in the xml_text field,
hence the "//*[name(.) = something" trick.  Is that the real problem?
And how does one namespace the xpath in xpath_table anyway?

Example data in the xml_text column:

<page-layout name="pi-chapter-layout"
collection-id="pi-brain"         type="dx"/>
<page-layout name="pi-chapter-layout"
collection-id="pi-gu"            type="dx"/>
<page-layout name="pi-chapter-layout"
collection-id="pi-gi"            type="dx"/>
<page-layout name="onc-page-layouts"
collection-id="di-oncology"      type="tsm"/>
<page-layout name="pain-management-procedure-chapter-layout"
collection-id="pain-management"  type="procedure"/>
<page-layout name="pain-management-procedure-chapter-layout"
collection-id="pain-management"  type="section-intro"/>
<page-layout name="procedure-chapter-layout"
collection-id="procedures-book"  type="procedure"/>
<page-layout name="procedure-chapter-layout"
collection-id="procedures-book"  type="section-intro"/>
<page-layout name="pathology-dx-page-layouts"
default="pathology-dx"/>
<page-layout name="pathology-pcf-overview-page-layouts"
default="pcf-overview"/>
<page-layout name="pathology-intro-page-layouts"
default="path-intro"/>
<page-layout name="pathology-intro-page-layouts"
default="specific-factor"/>

Rob Sargent <robjsargent@gmail.com> writes:
> When using pipe separated xpath statements to fill multiple columns of
> output,  there is no guarantee of correlation among the columns of the
> rows returned.

Yeah, this is a known problem mentioned in our TODO list.  Nobody has
any idea how to persuade libxml to do that, and the general opinion
seems to be that we shouldn't have designed xpath_table that way in the
first place ...

            regards, tom lane

On 04/04/2011 03:12 PM, Tom Lane wrote:
> Rob Sargent<robjsargent@gmail.com>  writes:
>> When using pipe separated xpath statements to fill multiple columns of
>> output,  there is no guarantee of correlation among the columns of the
>> rows returned.
>
> Yeah, this is a known problem mentioned in our TODO list.  Nobody has
> any idea how to persuade libxml to do that, and the general opinion
> seems to be that we shouldn't have designed xpath_table that way in the
> first place ...
>
>             regards, tom lane
Duly noted.  I think I can work around it now that I understand it a bit
better.  Should I put a comment in the on-line docs?

    Cheers,
    rjs