CODEWITHSUNDEEP
sqlxmlpostgresql
Raj
Raj

Reputation: 93

Select a specific record from xml record using Postgresql

I have a column in my table called data_xml that has records presented like this- 

<data user_id="A" user_id_type="1" date="2019-10-31"/>
<data user_id="B" user_id_type="2" date="2020-01-30"/>

I want to be able to pull user_id_type records from the column. I tried using split_part but because the xml data doesnot have a delimiter as seen above I am not sure how to proceed.

Does anyone have a better alternative.

Upvotes: 0

Views: 444

Answers (1)

user330315
user330315

Reputation:

You can use xpath() for that:

select (xpath('/data/@user_id_type', data_xml::xml))[1]
from the_table;

xpath() returns a array of all matches, that's why the [1] is needed.

Upvotes: 4

Related Questions