This discussion is archived
2 Replies Latest reply: Jul 10, 2013 4:52 AM by KenR RSS

XMLNAMESPACE with default value in XQuery

KenR Newbie
Currently Being Moderated

Need some help on how to resolve default namespace issue with the below XQuery. XQuery listed below works fine when the default namespace (xmlns="http://abc.org/SampleRequest/one") is removed from the SampleRequest XML element. Any help is appreciated.

 

 

with table1 AS
  (select xmltype(
'<SampleRequest xmlns:ns0="http://abc.org/SampleRequest/one" xmlns="http://abc.org/SampleRequest/one">
   <ns0:Appn>
      <AppnID xmlns="">1234567890</AppnID>
      <AppnStatCd xmlns="">RECEIVED</AppnStatCd>
   </ns0:Appn>
   <ns0:Indivd>
      <TtlCd xmlns="Mr"/>
      <FrstNm xmlns="">Joe</FrstNm>
      <MidNm xmlns=""/>
      <LastNm xmlns="">Bloggs</LastNm>
   </ns0:Indivd>
</SampleRequest>'
) xmlcol from dual
  )
  SELECT t.first_name,t.last_name
    from table1
    ,   xmltable(xmlnamespaces ('http://abc.org/SampleRequest/one' as "ns0"),
                  '/SampleRequest/ns0:Indivd' passing xmlcol
                  columns first_name     varchar2(20) path '//FrstNm',
                  last_name     VARCHAR2(20) PATH '//LastNm') t 
  ;
  • 1. Re: XMLNAMESPACE with default value in XQuery
    odie_63 Guru
    Currently Being Moderated

    You can declare a default namespace using the DEFAULT keyword :

    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#autoId7

    e.g.

    XMLTable( XMLNamespaces(default 'xmlns.example.org', 'another_ns' as "ns0"), ...

     

    In your example, the default namespace is the same as the one associated with the ns0 prefix, so you just have to prefix the SampleRequest element :

    SQL> with table1 AS  (

      2    select xmltype(

      3  '<SampleRequest xmlns:ns0="http://abc.org/SampleRequest/one" xmlns="http://abc.org/SampleRequest/one">

      4     <ns0:Appn>

      5        <AppnID xmlns="">1234567890</AppnID>

      6        <AppnStatCd xmlns="">RECEIVED</AppnStatCd>

      7     </ns0:Appn>

      8     <ns0:Indivd>

      9        <TtlCd xmlns="Mr"/>

    10        <FrstNm xmlns="">Joe</FrstNm>

    11        <MidNm xmlns=""/>

    12        <LastNm xmlns="">Bloggs</LastNm>

    13     </ns0:Indivd>

    14  </SampleRequest>'

    15  ) xmlcol

    16    from dual

    17  )

    18  select t.first_name, t.last_name

    19  from table1

    20     , xmltable(

    21         xmlnamespaces ( 'http://abc.org/SampleRequest/one' as "ns0" )

    22       , '/ns0:SampleRequest/ns0:Indivd'

    23         passing xmlcol

    24         columns first_name varchar2(20) path 'FrstNm',

    25                 last_name  varchar2(20) path 'LastNm'

    26       ) t

    27  ;

     

    FIRST_NAME           LAST_NAME

    -------------------- --------------------

    Joe                  Bloggs

  • 2. Re: XMLNAMESPACE with default value in XQuery
    KenR Newbie
    Currently Being Moderated

    Thanks much for the answer. Appreciate it.

Legend

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