parsing xml with PG 9.2.4

From: Mario Vlahovic <mario(dot)vlahovic(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: parsing xml with PG 9.2.4
Date: 2020-02-16 21:02:56
Message-ID: CA+iRdH9GzeGEWSRqSN9xsZGNSLesb+_xkwckgZOQ+XTSC-H3MA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello Developers,
I hope you can help me. I'm having troubles parsing some data from my psql
table, which I need for further manipulation.
So my query:

select program_information.description FROM program_information WHERE id =
8768787;

GIves me:

<?xml version="1.0"?>
<ProgramInformation>
<BasicDescription>
<Title type="original">Zla smrt</Title>
<Synopsis length="short">Pet prijateljev, starih nekaj čez dvajset let,
v samotni koči najde Knjigo mrtvih. S posnetka, ki so ga napravili
arheologi, izvedo, da je bilo starodavno besedilo odkrito med kandarijskimi
ruševinami sumerske civilizacije.</Synopsis>
<Keyword type="secondary"></Keyword>
<ParentalGuidance>
<mpeg7:ParentalRating href="rn:mpeg:MPAAParentalRatingCS:PG">
<mpeg7:Name>PG</mpeg7:Name>
</mpeg7:ParentalRating>
</ParentalGuidance>

* <CreditsList> <CreditsItem
role="urn:tva:metadata:TVARoleCS:ACTOR"> <PersonName>
<mpeg7:GivenName>Bruce</mpeg7:GivenName>
<mpeg7:FamilyName>Campbell</mpeg7:FamilyName> </PersonName>
</CreditsItem> <CreditsItem role="urn:tva:metadata:TVARoleCS:ACTOR">
<PersonName> <mpeg7:GivenName>Ellen</mpeg7:GivenName>
<mpeg7:FamilyName>Sandweiss</mpeg7:FamilyName> </PersonName>
</CreditsItem> <CreditsItem role="urn:tva:metadata:TVARoleCS:ACTOR">
<PersonName> <mpeg7:GivenName>Betsy</mpeg7:GivenName>
<mpeg7:FamilyName>Baker</mpeg7:FamilyName> </PersonName>
</CreditsItem> <CreditsItem
role="urn:tva:metadata:TVARoleCS:DIRECTOR"> <PersonName>
<mpeg7:GivenName>Sam</mpeg7:GivenName>
<mpeg7:FamilyName>Raimi</mpeg7:FamilyName> </PersonName>
</CreditsItem> </CreditsList>*
<ReleaseInformation>
<ReleaseDate>
<Year>1981</Year>
</ReleaseDate>
</ReleaseInformation>
</BasicDescription>
<AVAttributes>
<AudioAttributes>
<NumOfChannels>2</NumOfChannels>
</AudioAttributes>
</AVAttributes>
</ProgramInformation>

What I need is parsed data from <CreditsList>, GivenName + FamilyName for
all entries. I know it should be doable with xpath but I just can't get it
to work :/.

Please help.

Thanks,

Responses

Browse pgsql-general by date

  From Date Subject
Next Message maxzor 2020-02-16 21:38:29 1 Status of vertical clustered index - 2 Join using (fk_constraint) suggestion - 3 Status of pgsql's parser autonomization
Previous Message Peter J. Holzer 2020-02-16 13:51:21 Re: Cannot connect to postgresql-11 from another machine after boot