CODEWITHSUNDEEP
sqlxmloracle-databaseoracle11g
Nikitron
Nikitron

Reputation: 87

Oracle XML: extract child tag names

How to select all child tags name by xpath?

I have a table like this:

CREATE TABLE Foo (
  DATA VARCHAR2(4000) NOT NULL ENABLE
)

with some XML data in DATA filed like this:

<foo>
  <a>some</a>
  <b>another</b>
  <c>some else<c>
</foo>

I want to extract all tags from 2nd level:

select xmltype(DATA).extract('/foo/*').getRootElement() as MY_TAGS form Foo;

(I expected a list like this)

a
b
c

But I got a result of null values:

(null)
(null)
(null)

Upvotes: 1

Views: 2488

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

select * from xmltable('foo/*'  passing xmltype('<foo>
  <a>some</a>
  <b>another</b>
  <c>some else</c>
</foo>') 
columns 
tag_name varchar2(100) path 'name()',
tag_value varchar2(100) path 'text()'    
)

Upvotes: 2

Related Questions