Extract XML file stored in a postgresql table

Question

I have a XML file stored in postgresql table which contains array attribute type contains values such as

<Type>
      <V Idx="1">Cat</V>
      <V Idx="2">Mouse</V>
      <V Idx="3">Tree</V>
   </Type>

and I would like to extract the Idx value of "Cat" using postgresql query.

possible duplicate https://stackoverflow.com/questions/29550813/storing-xml-into-postgres — Srikrushna, Dec 26 '18 at 06:38

score 0 · Accepted Answer · answered Dec 26 '18 at 07:37

0

You can use xpath() to use an XPath expression extracting parts of XML. It'll return an array of the matches, so you need to select the desired index if you want a scalar.

To get the Idx attribute of the first /Type/V that has a text of Cat you could use:

SELECT (xpath('(/Type/V[text()="Cat"]/@Idx)[1]', nmuloc))[1] idx
       FROM elbat;

db<>fiddle

answered Dec 26 '18 at 07:37

sticky bit

36,626
12
31
42

It is working in but if xml header comes with url's it is not working. Can you please help me in that. For example : CASH C98 Cat Mouse Tree 100.00 150.00 200.00 – Anoop Jan 04 '19 at 10:03
From this xml i need to get the idx value of the array for which the text is "cat" and based on that Idx value I need to get totMiscCost from the next array attribute with the same Idx value – Anoop Jan 04 '19 at 10:08

Extract XML file stored in a postgresql table

1 Answers1