CODEWITHSUNDEEP
oracle-databaseplsqlxml-namespacesxmltable
Mateusz Gebroski
Mateusz Gebroski

Reputation: 1334

PL/SQL XML desctructuring: XVM-01081: [XPST0081] Invalid prefix

I am trying to read xml clob file with multiple namespaces. I am just literally out of ideas, spent few hours on this.

My test case looks like:

declare
  test_msg clob;
begin
  test_msg := to_clob(
       '
       <testReq xmlns="http://test/xxx/xxx-xxx/v1">
         <id>HelloWorld</id>
         <numer>HelloWorld</numer>
         <typ>COGR</typ>
         <czyAktywny>T</czyAktywny>
         <a:fieldone xmlns:a="http://xxx/yyy/zzz/v1">
             <a:sym>HelloWorld</a:sym>
             <a:sympod>HelloWorld</a:sympod>
         </a:fieldone>
     </testReq>
       '
  );
  -- Call the function
  :result := i_mypackage_pkg.odbierzReq(p_komunikatWej => test_msg,
                                                      p_logId => 12344);
end;

Where my implementation of xml_table looks like:

from xmltable(xmlnamespaces('http://test/xxx/xxx-xxx/v1' as "model"
                           ,'http://xxx/yyy/zzz/v1' as "adres"),
       '//model:testReq' passing xmlType(p_komunikatWej)
       columns
          id varchar2(20 char) path 'model:id'
         ,numer varchar2(20 char) path 'model:numer'
         ,typ varchar2(20 char) path 'model:typ'
         ,czyAktywny varchar2(1 char) path 'model:czyAktywny'
          -- dane adresowe punktu poboru
         ,kod varchar2(7 char) path 'adres:fieldone/a:sympod'
      ) t;

And the exception looks like this:

XVM-01081: [XPST0081] Invalid prefix

    1   declare namespace model=&quot;http://test/xxx/xxx-xxx/v1&quot;;declare namesp
    -                                                                                                                                         ^

I am literally out of ideas. Any help would be appreciated. Thanks

EDIT Solved by updating namespace alias:

'http://xxx/yyy/zzz/v1' as "a"

Upvotes: 0

Views: 491

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

As well as using a/adres consistently, you can simplify your paths by declaring a default namespace instead of adding model:

from xmltable(xmlnamespaces(default 'http://test/xxx/xxx-xxx/v1'
                           ,'http://xxx/yyy/zzz/v1' as "a"),
       '/testReq' passing xmlType(p_komunikatWej)
       columns
          id varchar2(20 char) path 'id'
         ,numer varchar2(20 char) path 'numer'
         ,typ varchar2(20 char) path 'typ'
         ,czyAktywny varchar2(1 char) path 'czyAktywny'
          -- dane adresowe punktu poboru
         ,kod varchar2(7 char) path 'a:fieldone/a:sympod'
      ) t;

db<>fiddle demo using a CTE instead of PL/SQL.

Upvotes: 1

Related Questions