2 Replies Latest reply: Feb 3, 2013 9:29 AM by Tomeo RSS

    How to parse data from XML with prefix?

    Tomeo
      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
          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
            Hi Alex,

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

            Many thanks,
            Tomas