Postgres query to fetch rows matching xpath criteria

Question

My xmldata is,

<Transaction >
   <UUID>2017-03-17T08:00:00-086F0ADD43</UUID>
   <SequenceNumber Type="a">1</SequenceNumber>
   <SequenceNumber Type="b">1</SequenceNumber>
</Transaction>
<Transaction >
   <UUID>2017-03-17T08:00:00-086F0ADD43</UUID>
   <SequenceNumber Type="a">2</SequenceNumber>
   <SequenceNumber Type="b">2</SequenceNumber>
</Transaction>

My current query is:

select xmldata, cast ((xpath('/Transaction/SequenceNumber[@Type="b" and text()="1"]/text()', xmldata)) AS TEXT) from tbltransaction

This results in all rows,

    xmldata    | xpath
---------------+-----
 <Transaction> | {1}
 <Transaction> | {}

But I want the result set with exact value like below,

     xmldata    | xpath
---------------+-----
 <Transaction> | {1}

How to modify the above query to fetch this?

Lukasz Szozda · Accepted Answer · 2018-05-25 06:49:18Z

1

How about filtering out using WHERE:

select s.r, xmldata 
from tbltransaction t
JOIN LATERAL (
    SELECT (xpath('/Transaction/SequenceNumber[@Type="b" and text()="1"]/text()',
            t.xmldata)::TEXT) AS r
     )s ON TRUE
WHERE s.r != '{}';

DBFiddle Demo

answered May 25, 2018 at 6:49

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Markus Winand · Accepted Answer · 2018-05-25 07:17:10Z

If you are using PostgreSQL 10 you can use XMLTABLE:

WITH data (xmldata) AS (VALUES('<Transaction >
   <UUID>2017-03-17T08:00:00-086F0ADD43</UUID>
   <SequenceNumber Type="a">1</SequenceNumber>
   <SequenceNumber Type="b">1</SequenceNumber>
</Transaction>'::xml),('
<Transaction >
   <UUID>2017-03-17T08:00:00-086F0ADD43</UUID>
   <SequenceNumber Type="a">2</SequenceNumber>
   <SequenceNumber Type="b">2</SequenceNumber>
</Transaction>'::xml))

SELECT *
  FROM data
     , XMLTABLE('/Transaction/SequenceNumber[@Type="b" and text()="1"]/text()'
                PASSING xmldata
                COLUMNS xpath XML PATH '.'
               ) t;

Gives you:

                    xmldata                     | xpath
------------------------------------------------+-------
 <Transaction >                                +| 1
    <UUID>2017-03-17T08:00:00-086F0ADD43</UUID>+|
    <SequenceNumber Type="a">1</SequenceNumber>+|
    <SequenceNumber Type="b">1</SequenceNumber>+|
 </Transaction>                                 |
(1 row)

Collectives™ on Stack Overflow

Postgres query to fetch rows matching xpath criteria

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related