This discussion is archived
4 Replies Latest reply: Jan 20, 2013 4:08 AM by odie_63 RSS

extracting xmlnamespace in oracle

974276 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for help Odie.
  • 3. Re: extracting xmlnamespace in oracle
    974276 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
     

Legend

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