2 Replies Latest reply: Jul 10, 2013 6:52 AM by Ken R RSS

    XMLNAMESPACE with default value in XQuery

    Ken R

      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">
            <AppnID xmlns="">1234567890</AppnID>
            <AppnStatCd xmlns="">RECEIVED</AppnStatCd>
            <TtlCd xmlns="Mr"/>
            <FrstNm xmlns="">Joe</FrstNm>
            <MidNm xmlns=""/>
            <LastNm xmlns="">Bloggs</LastNm>
      ) 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

          You can declare a default namespace using the DEFAULT keyword :



          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
            Ken R

            Thanks much for the answer. Appreciate it.