CODEWITHSUNDEEP
sqlxmloracle-databaseoracle11gxml-namespaces
Tom
Tom

Reputation: 7028

Extracting a node where xmlns is set to blank

I'm having difficulties extracting the value from certain nodes in an XML structure using XMLTABLE. Below query works perfectly when you remove the xmlns="" attribute from the SubListItem nodes. And as you can see, the XML already has a default namespace. I honestly have no clue how I can deal with this "blanking out" of the namespace on certain nodes like this. For further clarification, the creation of this XML is not within my control and is provided by a third-party. I've also changed the names of the nodes and the content from the delivered files while preserving the structure of the XML. 

SELECT f.airline, f.flightnumber, fl.gate
  FROM xmltable(
        xmlnamespaces(
                 default 'http://some/name.space',
                 'http://www.w3.org/2001/XMLSchema' as "xsd",
                 'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
                 ),
       'Body/Flight'
        passing xmltype(
'<?xml version="1.0" encoding="utf-16"?>
<Body xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://some/name.space">
  <Sender>
    <System>ConnectionManagement</System>
  </Sender>
  <Flight>
    <Airline>ABC</Airline>
    <Number>1234</Number>
    <SubList>
      <SubListItem xmlns="">
        <Gate>X</Gate>
      </SubListItem>
      <SubListItem xmlns="">
        <Gate>Y</Gate>
      </SubListItem>
      <SubListItem xmlns="">
        <Gate>Z</Gate>
      </SubListItem>
    </SubList>
  </Flight>
</Body>'
      )
      columns airline varchar2(100) path 'Airline'
     , flightNumber VARCHAR2(5) path 'Number'
     , subList XMLTYPE path 'SubList'
  ) f
, xmltable (
    xmlnamespaces( default 'http://some/name.space'),
    '/SubList/SubListItem'
    passing f.subList
    columns gate varchar2(5) path 'Gate'
  ) fl
;

How can I target the Gate node when the XML looks like this?

Upvotes: 0

Views: 214

Answers (1)

Alex Poole
Alex Poole

Reputation: 191425

Leave the default namespace alone in the second XMLTable, and specify a named namespace for the path you do have:

...
, xmltable (
    xmlnamespaces( 'http://some/name.space' as "ns"),
    '/ns:SubList/SubListItem'
    passing f.subList
    columns gate varchar2(5) path 'Gate'
  ) fl
;

AIRLINE    FLIGH GATE
---------- ----- -----
ABC        1234  X    
ABC        1234  Y    
ABC        1234  Z

The SubList still has to match that, but as the child nodes don't the default is incorrect the way you have it. If you remove the xmlns="" as you mentioned in the question then that inherits the namespace from its parent, so your default works. With that override to no-namespace you can't use a default.

Upvotes: 1

Related Questions