Re: parsing xml with PG 9.2.4 - Mailing list pgsql-general

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.vlahovic@gmail.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
sumerskecivilizacije.</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
withxpath but I just can't get it to work :/. 
>
> Please help.
>
> Thanks,