Oracle XMLTYPE extract first node

Question

I'm having an issue extracting the first node from a specific element of XML within Oracle.

This is the XML:

<data type="TestData" version="1">
  <MasterTable Name="TestMaster"/>
  <Table Name="TestTable1">Test</Table>
  <Table Name="TestTable2"/>
  <Table Name="TestTable3"/>
  <Table Name="TestTable4"/>
  <Table Name="TestTable5"/>
  <Table Name="TestTable6"/>
  <Table Name="TestTable7"/>
  <Table Name="TestTable8"/>
  <Table Name="TestTable9"/>
  <Table Name="TestTable10"/>
  <Table Name="TestTable11"/>
  <Table Name="TestTable12"/>
  <Table Name="TestTable13"/>
  <Table Name="TestTable14"/>
  <Fact Name="TestFact1"/>
</data>

I am trying to extract the value of the first Name element "TestTable1" and the text for the first Name element "Test".

I have the following queries which are just returning null:

select a.xml.extract('//Name[1]') from my_table a; --Attempting to return "TestTable1" from Name attribute 1



select a.xml.extract('//Name[1]/text()') from my_table a; --Attempting to return the text "Test" from Name attribute 1

Answer 1

with test as( select xmltype('<data type="TestData" version="1">
  <MasterTable Name="TestMaster"/>
  <Table OtherName="TestTable1">OtherTable</Table>
  <Table Name="TestTable1">Test</Table>
  <Table Name="TestTable2"/>
  <Table Name="TestTable3"/>
  <Table Name="TestTable4"/>
  <Table Name="TestTable5"/>
  <Table Name="TestTable6"/>
  <Table Name="TestTable7"/>
  <Table Name="TestTable8"/>
  <Table Name="TestTable9"/>
  <Table Name="TestTable10"/>
  <Table Name="TestTable11"/>
  <Table Name="TestTable12"/>
  <Table Name="TestTable13"/>
  <Table Name="TestTable14"/>
  <Fact Name="TestFact1"/>
</data>') x from dual)


select t.x.extract('//Table[@Name][1]'),t.x.extract('//Table[1]'),  t.x.extract('//Table[@Name][1]/text()'),t.x.extract('//Table[1]/text()'),t.x.extract('//Table[./text()][1]') from test t;

t.x.extract('//Table[@Name][1]') returns first 'Table'; with attribute Name t.x.extract('//Table[1]') returns first 'Table';
t.x.extract('//Table[./text()][1]') return first non-empty table element;