This discussion is archived
2 Replies Latest reply: Feb 3, 2013 7:29 AM by Tomeo RSS

How to parse data from XML with prefix?

Tomeo Newbie
Currently Being Moderated
Hi folks,

I have an XML which tags include prefixes. I would like to parse it using a select. Any ideas?

[Sample XML|http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_or.cgi?ico=46972501]

I tried this:
select xml
 from
   (  select 
            httpuritype('wwwinfo.mfcr.cz/cgi-bin/ares/darv_or.cgi?ico=46972501').getXML() xml
      from dual
    );
This just returns XML.


select  x.ico,
        x.org_name
    from dual d
         ,XMLTABLE('/are:Ares_odpovedi/are:Odpoved/D:Vypis_OR'
                    PASSING httpuritype('http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_or.cgi?ico=46972501').getXML()
                    COLUMNS
                       ico varchar2(50) PATH 'D:ZAU/D:ICO'
                      ,org_name varchar2(200) PATH 'D:ZAU/D:OF'
                    )  x
   where rownum = 1
 
ORA-19228: XPST0008 - undeclared identifier: prefix 'are' local-name 'are:Ares_odpovedi'
Any idea how to simply get all details into columns?

Thanks,
Tomas
  • 1. Re: How to parse data from XML with prefix?
    AlexAnd Guru
    Currently Being Moderated
    add xmlnamespaces to xmltable
    select  x.ico,
            x.org_name
        from XMLTABLE(
             xmlnamespaces('http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_answer_or/v_1.0.3' as "are"
             , 'http://wwwinfo.mfcr.cz/ares/xml_doc/schemas/ares/ares_datatypes/v_1.0.3' as "D"),
             '/are:Ares_odpovedi/are:Odpoved/D:Vypis_OR'
                        PASSING httpuritype('http://wwwinfo.mfcr.cz/cgi-bin/ares/darv_or.cgi?ico=46972501').getXML()
                        COLUMNS
                           ico varchar2(50) PATH 'D:ZAU/D:ICO'
                          ,org_name varchar2(200) PATH 'D:Vypis_OR/D:ZAU/D:OF'
                        )  x
       where rownum = 1
    not tested
  • 2. Re: How to parse data from XML with prefix?
    Tomeo Newbie
    Currently Being Moderated
    Hi Alex,

    yes, you're right. It works like a charm!

    Many thanks,
    Tomas

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points