how can I select XML attribute from oracle XMLTYPE column?

Question

I create table in oracle 12c like this

CREATE TABLE Test_xml
    (
       xml_data                       SYS.XMLTYPE
    )

and insert XML data like this into column xml_data

<?xml version="1.0" encoding="UTF-8"?>
<persons xmlns="http://XXXXX.XXX/schema/1.0" name="ABC">
  <person>
    <name1 value="AAAA" />
    <name2 value="CCCC" />
    <name3 value="XXXX" />
  </person>
  <person>
    <name1 value="11111" />
    <name2 value="22222" />
    <name3 value="33333" />
  </person>
</persons>

but when I try to select data out from table by query

SELECT xt.*
FROM   Test_xml x,
       XMLTABLE('/persons/person'
         PASSING x.xml_data
         COLUMNS 
           name1  VARCHAR2(2000) PATH 'name1/@value'
         ) xt;

not thing out from the query,How can i select name1 from the table?

Alex Poole · Accepted Answer · 2020-08-07 14:52:18Z

2

You can provide the (default, in this case) namespace as another argument to XMLTable:

SELECT xt.*
FROM   Test_xml x,
       XMLTABLE(
         xmlnamespaces(default 'http://XXXXX.XXX/schema/1.0'),
         '/persons/person'
         PASSING x.xml_data
         COLUMNS 
           name1  VARCHAR2(2000) PATH 'name1/@value'
         ) xt;

| NAME1 |
| :---- |
| AAAA  |
| 11111 |

db<>fiddle showing your original query, @Sayan's wildcard version (which gets nulls for some reason, even though it works with a CTE), and this default namespace version.

edited Aug 7, 2020 at 14:52

answered Aug 7, 2020 at 14:34

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sayan Malakshinov · Accepted Answer · 2020-08-07 15:50:18Z

1

Just replace /persons/person to /*:persons/*:person

SELECT/*+ NO_XML_QUERY_REWRITE */ xt.*
FROM   Test_xml x,
       XMLTABLE('/*:persons/*:person'
         PASSING x.xml_data
         COLUMNS 
           name1  VARCHAR2(2000) PATH '*:name1/@value'
         ) xt;

Full example:

with test_xml(xml_data) as (
select xmltype(q'[<?xml version="1.0" encoding="UTF-8"?>
<persons xmlns="http://XXXXX.XXX/schema/1.0" name="ABC">
  <person>
    <name1 value="AAAA" />
    <name2 value="CCCC" />
    <name3 value="XXXX" />
  </person>
  <person>
    <name1 value="11111" />
    <name2 value="22222" />
    <name3 value="33333" />
  </person>
</persons>]'
)
from dual
)
SELECT xt.*
FROM   Test_xml x,
       XMLTABLE('/*:persons/*:person'
         PASSING x.xml_data
         COLUMNS 
           name1  VARCHAR2(2000) PATH '*:name1/@value'
         ) xt;

NAME1
--------------------
AAAA
11111

edited Aug 7, 2020 at 15:50

answered Aug 7, 2020 at 12:44

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

14 Comments

MAXZZXAM Over a year ago

I try your query It work when it select from dual but if apply to the table, the result of name1 is null but return correct number of row in table. Am I missing something?

Sayan Malakshinov Over a year ago

@maxzzxam did you notice *:name1/@value?

Alex Poole Over a year ago

@SayanMalakshinov - I included your wildcard version in my db<>fiddle; and it doesn't work from a table. Not sure why, or why it would behave differently. Dumping the XML shows the same data type (58) but different lengths.

Sayan Malakshinov Over a year ago

@AlexPoole huh! You found a bug in columns clause :) I've included another variant into you db<>fiddle: dbfiddle.uk/…

Sayan Malakshinov Over a year ago

Re "different lengths": Though that's not ok, but it's understandable: oracle by default uses binary xml storage since 11.2 afair

|

Collectives™ on Stack Overflow

how can I select XML attribute from oracle XMLTYPE column?

2 Answers 2

Comments

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related