-1

I'm migrating a database from Oracle to PostgreSQL and have a problem with several Views.

I can't find an equivalent function for extractvalue() and xmltype()...

NVL(extractvalue(xmltype(METADATA), '/slb/structMap/div/mptr/@OTHERLOCTYPE', 'xmlns="http://www.nb.admin.ch/standards/slb" xmlns:marc="http://www.loc.gov/MARC21/slim"'), '')  

Here is the full View code:

  CREATE OR REPLACE FORCE VIEW "INGEST_TEST"."AIP_TYPE_INFO" ("AIP_ID", "AIP_VERSION", "TYPE_VALUE") AS 
select
 aip_id,
 aip_version,
case 
    when METADATA is not null then
        NVL(extractvalue(xmltype(METADATA), '/slb/structMap/div/mptr/@OTHERLOCTYPE', 'xmlns="http://www.nb.admin.ch/standards/slb" xmlns:marc="http://www.loc.gov/MARC21/slim"'), '')  
        || '/' ||
        NVL(extractvalue(xmltype(METADATA), '/slb/structMap/div/div[position() = 1]/mptr/@OTHERLOCTYPE', 'xmlns="http://www.nb.admin.ch/standards/slb" xmlns:marc="http://www.loc.gov/MARC21/slim"'), '') 
    else '/'
 end as type_value
from aip ;

The Oracle functions are:

extractvalue(): https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions052.htm#i1131042

xmltype(): https://docs.oracle.com/cd/B10500_01/appdev.920/a96616/arxml24.htm

I either never used those functions and the database I'm migrating. I only have the task to migrate it to PostgreSQL.

The NVL() function have its equivalent in PostgreSQL : COALESCE()

Does someone have an idea of how to manage this?

Here is an example of the XML content : https://www.dropbox.com/s/59mosi9ewcx67ga/XML_EXEMPLE.xml?dl=0 For this example the view should give me this:

zeitschrift/zeitschriftennummer

  • I think xpath() is probably what you are looking for – a_horse_with_no_name Sep 6 '18 at 8:32
  • I have looked at xpath() but couldn't manage to do it because it don't use the same arguments.. can you try to show me an example please ? – Renard Sep 6 '18 at 8:37
  • 2
    You need to provide some sample XML and what you expect as the output. xpath() is pretty much the same as Oracle's extractvalue(). Edit your question. Do not provide additional information as answers. – a_horse_with_no_name Sep 6 '18 at 8:43
0

Postgres' XML processing is a bit stricter when it comes to XMNL namespaces, so the xpath expression has to prefix every element with the correct namespace.

Based on your sample XML, only the http://www.nb.admin.ch/standards/slb namespace is needed, so the view should be something like this:

CREATE OR REPLACE FORCE VIEW AIP_TYPE_INFO
AS 
select aip_id,
       aip_version,
       case 
         when METADATA is not null then
            concat_ws('/', (xpath('/slb:slb/slb:structMap/slb:div/slb:mptr/@OTHERLOCTYPE', METADATA::xml, t.nsp))[1],
                           (xpath('/slb:slb/slb:structMap/slb:div/slb:div[position() = 1]/slb:mptr/@OTHERLOCTYPE', metadata::xml, t.nsp))[1])
         else '/'
       end as type_value
from aip, 
     (values (array[array['slb', 'http://www.nb.admin.ch/standards/slb']])) as t(nsp);

The part:

, (values (array[array['slb', 'http://www.nb.admin.ch/standards/slb']]))

simply creates a single row with an array of the needed namespace definitions. It's there to make the actual xpath() call shorter. You can replace both the t.nsp inside the function with array[array['slb', 'http://www.nb.admin.ch/standards/slb']] if you don't like it.

Note that xpath() returns an array of matches. That's why (xpath(...))[1] is needed: it simply gets the first match from the array.

concat_ws() will cope with NULL values automatically - this is slightly different that the Oracle version. If both xpath() expressions evaluate to null the Oracle view would return '/' but concat_ws() would return an empty string. If you don't want that, you need to use the same approach as in Oracle.

Online example: http://rextester.com/FANG87147

  • Thank you for your answere. extractvalue(): docs.oracle.com/cd/B19306_01/server.102/b14200/… xmltype(): docs.oracle.com/cd/B10500_01/appdev.920/a96616/arxml24.htm I either never used those functions and the database I'm migrating. I only have the task to migrate it to postgreSQL. The NVL() function have its equivalent in postgreSQL : COALESCE() – Renard Sep 6 '18 at 8:25
  • Here is an example of the XML content : dropbox.com/s/59mosi9ewcx67ga/XML_EXEMPLE.xml?dl=0 For this example the view should give me this : zeitschrift/zeitschriftennummer – Renard Sep 6 '18 at 10:26
  • Thank you for your example. I will try to adapt it to work. – Renard Sep 6 '18 at 10:29
  • I try to convert another view by following the same logic as your example but I have an error "invalid XPath expression". I think it's because I have another namespace but I don't know how to delcare it.. Can you help me for this one too ? EXTRACTVALUE (XMLTYPE (metadata), '/slb/amdSec/techMD/mdWrap/xmlData/nlnz:Object/nlnz:Processes/nlnz:Process[(./nlnz:Type="Originalcreator")]/nlnz:ProcessCreator', 'xmlns="nb.admin.ch/standards/slb" xmlns:marc="loc.gov/MARC21/slim" xmlns:nlnz="natlib.govt.nz/presmet"' ) original_Creator – Renard Sep 6 '18 at 13:04
  • This View uses the same XML source as the other one. – Renard Sep 6 '18 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.