Extract data from a Postgres XML column using XPath

Question

I have an XML data like this in my postgres table

<?xml version="1.0" encoding="UTF-8"?>
<Mydata>
   <LogNumber>10000</LogNumber>
   <LicenseNumber>XEU895</LicenseNumber>
   <LineCode>
      <V Idx="1">A</V>
      <V Idx="2">B</V>
      <V Idx="3">C</V>
      <V Idx="4">D</V>
   </LineCode>
   <OptionNo>
      <V Idx="1">999</V>
      <V Idx="2">27049</V>
      <V Idx="3">27049</V>
      <V Idx="4">999</V>
   </OptionNo>
</Mydata>

Using Xpath query I want to select the data like this.

LogNumber   LicenseNumber   LineCode    OptionNo
10000         XEU895            A           999
10000         XEU895            B           27049
10000         XEU895            C           27049
10000         XEU895            D           999

I have tried so many XPath queries, but all are giving me only one value from the LineCode and OptionNo node. So any help appreciated

klin · Accepted Answer · 2019-04-25 14:19:12Z

4

The xpath() function returns an array of XML values. Use unnest() to get elements of arrays returned by the function applied to nodes pointed by the first argument xpath:

with t(x) as ( 
values (
    '<?xml version="1.0" encoding="UTF-8"?>
    <Mydata>
       <LogNumber>10000</LogNumber>
       <LicenseNumber>XEU895</LicenseNumber>
       <LineCode>
          <V Idx="1">A</V>
          <V Idx="2">B</V>
          <V Idx="3">C</V>
          <V Idx="4">D</V>
       </LineCode>
       <OptionNo>
          <V Idx="1">999</V>
          <V Idx="2">27049</V>
          <V Idx="3">27049</V>
          <V Idx="4">999</V>
       </OptionNo>
    </Mydata>'::xml)
)

select 
    (xpath('./LogNumber/text()', x))[1] as "LogNumber",
    (xpath('./LicenseNumber/text()', x))[1] as "LicenseNumber",
    unnest(xpath('./LineCode/V/text()', x)) as "LineCode",
    unnest(xpath('./OptionNo/V/text()', x)) as "OptionNo"
from t

 LogNumber | LicenseNumber | LineCode | OptionNo 
-----------+---------------+----------+----------
 10000     | XEU895        | A        | 999
 10000     | XEU895        | B        | 27049
 10000     | XEU895        | C        | 27049
 10000     | XEU895        | D        | 999
(4 rows)

Play with it in Db-fiddle.

answered Apr 25, 2019 at 14:19

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Binu V Pillai Over a year ago

Thanks for the quick response. I have one more request. How can I list the Idx value of the OptionNo as a column with the above data ?

klin Over a year ago

You can get an attribute value using @, e.g. unnest(xpath('./LineCode/V/@Idx', x)) as "Idx"

Evgeny Nozdrev Over a year ago

Note that unnest() expands array to table. Usually it is used in FROM, but here in SELECT two independent expandings lead to zip two tables! 1st row with 1st one, 2nd with 2nd, ... .

Collectives™ on Stack Overflow

Extract data from a Postgres XML column using XPath

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest