Re: XML/XPath issues: text/CDATA in XMLTABLE, XPath evaluated with wrong context

Hi,
The below statement needs to be executed before running the query to
replicate the issue

update xmldata set data = regexp_replace(data::text, '791',
'<!--ah-->7<!--oh-->9<!--uh-->1')::xml;

On Thu, 28 Feb 2019 at 17:55, Pavel Stehule <pavel.stehule@gmail.com> wrote:

>
>
> čt 28. 2. 2019 v 10:31 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
> napsal:
>
>>
>>
>> čt 28. 2. 2019 v 9:58 odesílatel Ramanarayana <raam.soft@gmail.com>
>> napsal:
>>
>>> Hi,
>>>
>>>         I have tested the three issues fixed in patch 001. Array Indexes
>>> issue is still there.Running the following query returns ERROR: more
>>> than one value returned by column XPath expression
>>>
>>> SELECT xmltable.*
>>> FROM (SELECT data FROM xmldata) x,
>>> LATERAL XMLTABLE('/ROWS/ROW'
>>> PASSING data
>>> COLUMNS
>>> country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
>>> size_text float PATH 'SIZE/text()',
>>> size_text_1 float PATH 'SIZE/text()[1]',
>>> size_text_2 float PATH 'SIZE/text()[2]',
>>> "SIZE" float, size_xml xml PATH 'SIZE')
>>>
>>> The other two issues are resolved by this patch.
>>>
>>
> I tested xmltable-xpath-result-processing-bugfix-6.patch
>
> and it is working
>
> postgres=# SELECT  xmltable.*
> postgres-#    FROM (SELECT data FROM xmldata) x,
> postgres-#         LATERAL XMLTABLE('/ROWS/ROW'
> postgres(#                          PASSING data
> postgres(#                          COLUMNS
> postgres(#                                   country_name text PATH
> 'COUNTRY_NAME/text()' NOT NULL,
> postgres(#                                   size_text float PATH
> 'SIZE/text()',
> postgres(#                                   size_text_1 float PATH
> 'SIZE/text()[1]',
> postgres(#                                   size_text_2 float PATH
> 'SIZE/text()[2]',
> postgres(#                                   "SIZE" float, size_xml xml
> PATH 'SIZE')  ;
> ┌──────────────┬───────────┬─────────────┬─────────────┬──────┬────────────────────────────┐
>
> │ country_name │ size_text │ size_text_1 │ size_text_2 │ SIZE │
> size_xml          │
> ╞══════════════╪═══════════╪═════════════╪═════════════╪══════╪════════════════════════════╡
>
> │ Australia    │         ∅ │           ∅ │           ∅ │    ∅ │
> ∅                          │
> │ China        │         ∅ │           ∅ │           ∅ │    ∅ │
> ∅                          │
> │ HongKong     │         ∅ │           ∅ │           ∅ │    ∅ │
> ∅                          │
> │ India        │         ∅ │           ∅ │           ∅ │    ∅ │
> ∅                          │
> │ Japan        │         ∅ │           ∅ │           ∅ │    ∅ │
> ∅                          │
> │ Singapore    │       791 │         791 │           ∅ │  791 │ <SIZE
> unit="km">791</SIZE> │
> └──────────────┴───────────┴─────────────┴─────────────┴──────┴────────────────────────────┘
>
> (6 rows)
>
> Regards
>
> Pavel
>
>
>>
>> what patches you are used?
>>
>> Regards
>>
>> Pavel
>>
>>
>>> --
>>> Cheers
>>> Ram 4.0
>>>
>>

-- 
Cheers
Ram 4.0