CODEWITHSUNDEEP
xmlpostgresqlxpathpostgresql-9.1
Tomas Greif
Tomas Greif

Reputation: 22623

Xpath in Postgresql

I have the following XML data stored in PostgreSQL database (in column named result, type is XML):

<are:Ares_odpovedi xmlns:are="http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_answer_adr/v_2.0.0" xmlns:dtt="http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_datatypes/v_1.0.4" xmlns:udt="http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/uvis_datatypes/v_1.0.1" odpoved_datum_cas="2013-09-25T13:20:30" odpoved_pocet="1" odpoved_typ="Stdadr" vystup_format="XML" xslt="klient" validation_XSLT="http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_odpovedi.xsl" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_answer_adr/v_2.0.0 http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_answer_adr/v_2.0.0/ares_answer_adr.xsd" Id="ares">
  <are:Odpoved>
    <dtt:Pomocne_ID>1</dtt:Pomocne_ID>
    <dtt:Stdadr_dotaz>
      <dtt:Typ_odkazu>0</dtt:Typ_odkazu>
      <dtt:Adresa_ARES>
        <dtt:Nazev_obce>Hlučín</dtt:Nazev_obce>
        <dtt:Nazev_ulice>Jana Nerudy</dtt:Nazev_ulice>
      </dtt:Adresa_ARES>
    </dtt:Stdadr_dotaz>
    <dtt:Stdadr_odpoved>
      <dtt:Vsechna_slova>
        <dtt:Pocet_nalezenych>1</dtt:Pocet_nalezenych>
        <dtt:Pocet_navracenych>1</dtt:Pocet_navracenych>
        <dtt:Seznam_navracenych>     
          <dtt:Priz_adr>
            <dtt:Priznaky>99</dtt:Priznaky>
            <dtt:Vstup>110111190010111119111910</dtt:Vstup>
            <dtt:Vystup>66691169669</dtt:Vystup>
            <dtt:Prirazeni>22222229111122222921192</dtt:Prirazeni>
          </dtt:Priz_adr>
        </dtt:Seznam_navracenych>
      </dtt:Vsechna_slova>
    </dtt:Stdadr_odpoved>
  </are:Odpoved>
</are:Ares_odpovedi>

I would like to extract some values using xpath(). To extract Nazev_ulice I have so far:

select 
 xpath('//dtt:Nazev_ulice/text()',result,
 ARRAY[
  ARRAY['are','http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_answer_adr/v_2.0.0'],
  ARRAY['dtt', 'http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/uvis_datatypes/v_1.0.1']
])    
from xml_data;

This however returns empty result ({}). What would be the correct syntax to return value for element Nazev_ulice?

Note: I've tried to setup SQLFiddle demo, but for my query I get: Method org.postgresql.jdbc3.Jdbc3Array.getArrayImpl(long,int,Map) is not yet implemented

SQLFiddle

Upvotes: 0

Views: 1783

Answers (1)

nwellnhof
nwellnhof

Reputation: 33618

The dtt namespace in your query seems to be wrong.

In your XML:

xmlns:dtt="http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_datatypes/v_1.0.4"

In your query:

ARRAY['dtt', 'http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/uvis_datatypes/v_1.0.1']

Upvotes: 1

Related Questions