From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | scary xpath_table behaviour |
Date: | 2011-04-04 19:57:22 |
Message-ID: | 4D9A22A2.1020705@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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"/>
From | Date | Subject | |
---|---|---|---|
Next Message | Edoardo Serra | 2011-04-04 20:18:43 | Foreign key and locking problem |
Previous Message | Jens Wilke | 2011-04-04 19:49:24 | Re: Autovacuum firing up during my manual vacuum on same table |