parsing xml with PG 9.2.4 - Mailing list pgsql-general

From Mario Vlahovic
Subject parsing xml with PG 9.2.4
Date
Msg-id CA+iRdH9GzeGEWSRqSN9xsZGNSLesb+_xkwckgZOQ+XTSC-H3MA@mail.gmail.com
Whole thread Raw
Responses Re: parsing xml with PG 9.2.4  (Jimmy Angelakos <jimmy@2ndquadrant.com>)
List 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,

pgsql-general by date:

Previous
From: "Peter J. Holzer"
Date:
Subject: Re: Cannot connect to postgresql-11 from another machine after boot
Next
From: Imre Samu
Date:
Subject: Re: 1 Status of vertical clustered index - 2 Join using(fk_constraint) suggestion - 3 Status of pgsql's parser autonomization