Select a specific record from xml record using Postgresql

Question

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.

score 4 · Accepted Answer · 2020-02-05 06:34:00Z

4

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.

edited Feb 5, 2020 at 6:34

answered Feb 4, 2020 at 21:59

user330315

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

5 Comments

Raj Over a year ago

i get the following error when I try the above in DBviz - function xpath(unknown, character varying) does not exist. Jint: no function matches the given name and argument type. you may need to add explicit type casts. Not sure why?

Raj Over a year ago

Also I need to pull all the user_id_type. Should I have [2] instead of [1] ?

user330315 Over a year ago

Then apparently you don't have a xml but a varchar column - you need to cast it to xml. Use @user_id_type if you want that attribte

Raj Over a year ago

thanks a_horse_with_no_name How do I cast varchar to xml using the xpath() function

Raj Over a year ago

Thank you @a_horse_with_no_name

Collectives™ on Stack Overflow

Select a specific record from xml record using Postgresql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest