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"/>
