Re: parsing xml with PG 9.2.4

Hi Mario,

First off, as you will be aware, 9.2 is quite an old Postgres version
and is currently unsupported - for security reasons alone, you should
upgrade ASAP.

Regardless, this query should work for you:

SELECT xpath('/ProgramInformation/BasicDescription/CreditsList',
program_information.description, NAMESPACE_ARRAY) FROM
program_information WHERE id = 8768787;

where NAMESPACE_ARRAY needs to contain your definition for prefix
mpeg7, otherwise you'll get "Namespace prefix is not defined" errors
when parsing.

You will find more Xpath guidance here:
https://www.postgresql.org/docs/9.2/functions-xml.html#FUNCTIONS-XML-PROCESSING

Best regards,
Jimmy

On Mon, 17 Feb 2020 at 16:32, Mario Vlahovic <mario(dot)vlahovic(at)gmail(dot)com> wrote:
>
> 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,