4 Replies Latest reply: Sep 8, 2011 7:08 AM by odie_63 RSS

    Why extract() is not working without namespace

    Rakin
      Hi,

      Why extract() is not working without menthioning the namespace, I read namespace is optional parameter.


      Also How can I remove the namespace from a node which is in a xmltype variable.
      eg:
      <FruitList xmlns="https://abc.efg.org/FOOD-ITEMS">
      <fruitname>Orange </fruitname>
      <fruitname>Apple</fruitname>
      <fruitname>Banana</fruitname>
      </FruitList>




      Thanks in advance.
        • 1. Re: Why extract() is not working without namespace
          odie_63
          Hi,

          You do need that parameter if you want to extract a node belonging to a particular namespace.

          For example,
          extract(xmldoc, '/FruitList/fruitname[3]', 'xmlns="https://abc.efg.org/FOOD-ITEMS"')
          should return
          <fruitname>Banana</fruitname>
          Also How can I remove the namespace from a node which is in a xmltype variable.
          Please see my answer in this thread for some options :
          {thread:id=2274308}

          Edited by: odie_63 on 8 sept. 2011 12:19
          • 2. Re: Why extract() is not working without namespace
            AlexAnd
            + regexp_replace =)
            • 3. Re: Why extract() is not working without namespace
              Rakin
              Hi,

              I did try with createNonSchemaBasedXML, but it not giving the result.

              When I try with XQuery example :

              SQL> DECLARE
              2 tempXML xmltype := xmltype(
              3 '<patients xmlns="http://patient.entity.mdmreference.prehl7sys.com/2011">
              4 <patient>
              5 <id root="3.45.980.7.893478.5599" extension="12879"/>
              6 <patient_Person>
              7 <id root="3.45.980.7.893478.5599" extension="020-33-3333" assigningAuthorityName="CA" validTime="2011-08-27"/>
              8 <nm use="L">
              9 <family/>
              10 <given>Seshan</given>
              11 <given>S</given>
              12 </nm>
              13 </patient_Person>
              14 </patient>
              15 </patients>');
              16
              17 result CLOB;
              18
              19 BEGIN
              20
              21 SELECT XMLSerialize(content
              22 XMLQuery(
              23 'declare function local:removeNS($e as element()) as element()
              24 {
              25 element { QName("", local-name($e)) }
              26 {
              27 for $i in $e/node()|$e/attribute::*
              28 return typeswitch ($i)
              29 case element() return local:removeNS($i)
              30 default return $i
              31 }
              32 }; (::)
              33 local:removeNS($f/child::*)'
              34 passing tempxml as "f"
              35 returning content
              36 )
              37 as clob indent
              38 )
              39 INTO result
              40 FROM dual;
              41
              42 dbms_output.put_line(result);
              43
              44 END;
              45 /

              I am getting the following error , when I run your above example, and also when I run my xml with registered namespace.

              ORA-19112: error raised during evaluation: oracle.xquery.XQException: Parsing failed 'Lexical error at line 10, column 14. Encountered: ")" (41), after : ""'


              My all purpose to remove the name space is to run a while loop in an XML like the below example,(But my XML has name space). when I remove the namespace I am getting the
              output in while loop.

              PROCEDURE xmltest IS v_userlist XMLType; v_count NUMBER(38) := 1; BEGIN /* define XML variable */ v_userlist := XMLType('<?xml version="1.0"?> <users> <user> <name>user1</name> </user> <user> <name>user2</name> </user> <user> <name>user3</name> </user> </users>'); /* for each user, print out their name (each element can be extracted using xpath '//user[1]' '//user[2]' etc) */ WHILE v_userlist.existsNode('//user[' || v_count || ']') = 1 LOOP dbms_output.put_line(v_userlist.extract('//user[' || v_count || ']/name/text()').getStringVal()); v_count := v_count + 1; END LOOP; END;
              • 4. Re: Why extract() is not working without namespace
                odie_63
                I am getting the following error , when I run your above example, and also when I run my xml with registered namespace.

                ORA-19112: error raised during evaluation: oracle.xquery.XQException: Parsing failed 'Lexical error at line 10, column 14. Encountered: ")" (41), after : ""'
                Line 32 :
                32 }; (::)
                Add a space in between the comment delimiters :
                (: :)
                My all purpose to remove the name space is to run a while loop in an XML like the below example,(But my XML has name space). when I remove the namespace I am getting the
                output in while loop.
                Can't you use the namespace parameter in extract function then?

                Anyway, using a loop and dynamic XPath is not the right way to do what you want.
                Use XMLTable to directly parse the document into relational data.
                SQL> var xml_userlist varchar2(4000)
                SQL> begin
                  2   :xml_userlist := '<?xml version="1.0"?>
                  3  <users xmlns="some.namespace.uri">
                  4   <user><name>user1</name></user>
                  5   <user><name>user2</name></user>
                  6   <user><name>user3</name></user>
                  7  </users>';
                  8  end;
                  9  /
                 
                PL/SQL procedure successfully completed
                 
                SQL> SELECT x.username
                  2  FROM XMLTable(
                  3         XMLNamespaces(default 'some.namespace.uri')
                  4       , '/users/user'
                  5         passing xmltype(:xml_userlist)
                  6         columns username varchar2(30) path 'name'
                  7       ) x
                  8  ;
                 
                USERNAME
                ------------------------------
                user1
                user2
                user3
                 
                Edited by: odie_63 on 8 sept. 2011 13:22

                Edited by: odie_63 on 8 sept. 2011 14:07