3 Replies Latest reply: Apr 5, 2012 6:19 AM by odie_63 RSS

    xmltype extractValue with namespace raise exception "invalid token"

    916932
      Hello. I have XML which stored as XMLTYPE and with content such as:

      +<notificationEF>+
      +<oos:id>2812301</oos:id>+

      When I trying to execute

      extractValue(XML_CLOB, 'notificationEF/oos:id')

      I catch exception "LPX-00601: Invalid token in: 'notificationEF/oos:id'"

      What's wrong? And how can i fix this?

      Thank you.
        • 1. Re: xmltype extractValue with namespace raise exception "invalid token"
          odie_63
          You must declare the namespace prefix in extractvalue() function :
          extractValue(XML_CLOB, 'notificationEF/oos:id', 'xmlns:oos="namespace.uri.goes.here"')
          • 2. Re: xmltype extractValue with namespace raise exception "invalid token"
            916932
            Thanks!

            select
            extractValue(
            xmltype('<?xml version="1.0" encoding="UTF-8"?><export xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://example.ru/oos/export/1" xmlns:oos="http://example.ru/oos/types/1"><contract><oos:id>4711561</oos:id></contract></export>'),
            'export/contract/oos:id', 'xmlns="http://example.ru/oos/export/1" xmlns:oos="http://example.ru/oos/types/1"') title
            from dual

            return "4711561", and it correct. But how can I return many values, for example

            <?xml version="1.0" encoding="UTF-8"?><export xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://example.ru/oos/export/1" xmlns:oos="http://example.ru/oos/types/1"><contract><oos:id>4711561</oos:id><oos:id>4711561</oos:id></contract></export>

            extractValue is not working, extract return

            <oos:id xmlns:oos="http://example.ru/oos/types/1">4711561</oos:id><oos:id xmlns:oos="http://example.ru/oos/types/1">4711561</oos:id>

            Edited by: 913929 on 05.04.2012 3:50
            • 3. Re: xmltype extractValue with namespace raise exception "invalid token"
              odie_63
              Here's one way :
              SQL> var xmldoc varchar2(4000)
              SQL> 
              SQL> begin
                2   :xmldoc :=
                3  '<?xml version="1.0" encoding="UTF-8"?>
                4  <export xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                5          xmlns="http://example.ru/oos/export/1"
                6          xmlns:oos="http://example.ru/oos/types/1">
                7   <contract>
                8    <oos:id>4711561</oos:id>
                9    <oos:id>4711561</oos:id>
               10   </contract>
               11  </export>';
               12  end;
               13  /
               
              PL/SQL procedure successfully completed
               
              SQL> SELECT x.id
                2  FROM XMLTable(
                3         XMLNamespaces(
                4           default 'http://example.ru/oos/export/1'
                5         , 'http://example.ru/oos/types/1' as "oos"
                6         )
                7       , '/export/contract/oos:id'
                8         passing xmltype(:xmldoc)
                9         columns id number path '.'
               10       ) x
               11  ;
               
                      ID
              ----------
                 4711561
                 4711561