4 Replies Latest reply: Jan 20, 2013 6:08 AM by odie_63 RSS

    extracting xmlnamespace in oracle

    974276
      Hi Gurus,

      we are getting xml files with different xmlnamespaces and we want to extract and pass it to default name space.

      below is the example xml file:

      <?xml version="1.0" encoding="UTF-8"?>
      <Feed xmlns="http://www.abc.com/xs/sbc/data/5.7" name="abc" extractDate="2012-12-07T06:08:00.272-06:00">
      <Article number="18" disabled="false" removed="false">
      <Source>abc</Source>
      <Id>18</Id>
      </Article>
      </Feed>

      xmlns (xmlns="http://www.abc.com/xs/sbc/data/5.7") value is not fixed it is changing in other file it is coming as (xmlns="http://www.abc.com/xs/sbc/data/5.2")

      please guide me the way for extracting the namespace and passing it.
        • 1. Re: extracting xmlnamespace in oracle
          odie_63
          SQL> with sample_data as (
            2    select xmltype('<?xml version="1.0" encoding="UTF-8"?>
            3  <Feed xmlns="http://www.abc.com/xs/sbc/data/5.7" name="abc" extractDate="2012-12-07T06:08:00.272-06:00">
            4  <Article number="18" disabled="false" removed="false">
            5  <Source>abc</Source>
            6  <Id>18</Id>
            7  </Article>
            8  </Feed>') xmldoc
            9    from dual
           10  )
           11  select xmlcast(
           12           xmlquery('namespace-uri(.)' passing t.xmldoc returning content)
           13           as varchar2(2000)
           14         ) ns_uri
           15  from sample_data t ;
           
          NS_URI
          --------------------------------------------------------------------------------
          http://www.abc.com/xs/sbc/data/5.7
           
          • 2. Re: extracting xmlnamespace in oracle
            974276
            Thanks for help Odie.
            • 3. Re: extracting xmlnamespace in oracle
              974276
              Hi Odie,

              i want to store xmlnamespace in variable and wanted to use that variable in below query, but it is giving error.

              Please suggest the way pathforward.

              sample xml
              <?xml version="1.0" encoding="UTF-8"?>
              <Feed xmlns="http://www.abc.com/xs/sbc/data/5.7" name="abc" extractDate="2012-12-07T06:08:00.272-06:00">
              <Article number="18" disabled="false" removed="false">
              <Source>abc</Source>
              <Id>18</Id>
              </Article>
              </Feed>

              CREATE TABLE test_xml
              (
              XML_COL SYS.XMLTYPE,
              FILENAME CHAR(1000 BYTE)
              );

              INSERT INTO test_xml(FILENAME,XML_COL) VALUES ('abc.xml',XMLTYPE (BFILENAME ('IN_XML', 'abc.xml'),NLS_CHARSET_ID ('UTF8')));

              declare
              v_namespace varchar2(2000);
              begin
              select xmlcast(
              xmlquery('namespace-uri(.)' passing xml_col returning content)
              as varchar2(256)) into v_namespace
              from test_xml;
              select * from
              test_xml t,
              XMLTABLE (xmlnamespaces (default v_namespace),'Feed'
              passing t.xml_col COLUMNS
              FEEDPOSITION FOR ORDINALITY,
              name VARCHAR2 (30 BYTE) PATH '@name'
              ,extractDate VARCHAR2 (30 BYTE) PATH '@extractDate'
              )(+) feed
              end;
              • 4. Re: extracting xmlnamespace in oracle
                odie_63
                You could use wildcards but then you would only be able to access attributes (since there are in no namespace by default) :
                SQL> DECLARE
                  2  
                  3    v_namespace    varchar2(256);
                  4    v_name         varchar2(30);
                  5    v_source       varchar2(30);
                  6    v_extractDate  timestamp with time zone;
                  7  
                  8  BEGIN
                  9  
                 10    select x.name, x.extractDate, x.source
                 11    into  v_name, v_extractDate, v_source
                 12    from test_xml t
                 13       , xmltable ('*'
                 14           passing t.xml_col
                 15           columns
                 16             feedposition for ordinality
                 17           , name         varchar2 (30)            path '@name'
                 18           , extractDate  timestamp with time zone path '@extractDate'
                 19           , source       varchar2(30)             path 'Article/Source'
                 20         ) x ;
                 21  
                 22    dbms_output.put_line('name        = '||v_name);
                 23    dbms_output.put_line('extractDate = '||v_extractDate);
                 24    dbms_output.put_line('source      = '||v_source);
                 25  
                 26  END;
                 27  /
                 
                name        = abc
                extractDate = 07/12/12 06:08:00.272000 -06:00
                source      = 
                 
                PL/SQL procedure successfully completed
                 
                To benefit from optimization features as if the namespace was harcoded, you'll need to make the whole query dynamic :
                SQL> DECLARE
                  2  
                  3    v_namespace    varchar2(256);
                  4    v_name         varchar2(30);
                  5    v_source       varchar2(30);
                  6    v_extractDate  timestamp with time zone;
                  7  
                  8    v_query        varchar2(4000) :=
                  9  q'#select x.name, x.extractDate, x.source
                 10  from test_xml t
                 11     , xmltable (
                 12         xmlnamespaces(default '<XMLNS>')
                 13       , '/Feed'
                 14         passing t.xml_col
                 15         columns
                 16           feedposition for ordinality
                 17         , name         varchar2 (30)            path '@name'
                 18         , extractDate  timestamp with time zone path '@extractDate'
                 19         , source       varchar2(30)             path 'Article/Source'
                 20       ) x #' ;
                 21  
                 22  BEGIN
                 23  
                 24    select xmlcast(
                 25             xmlquery('namespace-uri(.)' passing xml_col returning content)
                 26             as varchar2(256)
                 27           )
                 28    into v_namespace
                 29    from test_xml;
                 30  
                 31    execute immediate replace(v_query, '<XMLNS>', v_namespace)
                 32                      into v_name, v_extractDate, v_source ;
                 33  
                 34    dbms_output.put_line('name        = '||v_name);
                 35    dbms_output.put_line('extractDate = '||v_extractDate);
                 36    dbms_output.put_line('source      = '||v_source);
                 37  
                 38  END;
                 39  /
                 
                name        = abc
                extractDate = 07/12/12 06:08:00.272000 -06:00
                source      = abc
                 
                PL/SQL procedure successfully completed