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 stackoverflow.com/questions/29550813/storing-xml-into-postgres — Srikrushna
– Srikrushna, Commented Dec 26, 2018 at 6:38

sticky bit · Accepted Answer · 2018-12-26 07:37:51Z

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, 2018 at 7:37

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

2 Comments

Anoop Over a year ago

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 : <sample-test-data-history xmlns="dmotorworks.com/sample-test-data-history"> <payPaymentCode> <V Idx="1">CASH</V> </payPaymentCode> <StatusCode>C98</StatusCode> <Type> <V Idx="1">Cat</V> <V Idx="2">Mouse</V> <V Idx="3">Tree</V> </Type> <totMiscCost> <V Idx="1">100.00</V> <V Idx="2">150.00</V> <V Idx="3">200.00</V> </totMiscCost> </sample-test-data-history>

Anoop Over a year ago

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

Collectives™ on Stack Overflow

Extract XML file stored in a postgresql table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related