CODEWITHSUNDEEP
sqlxmloracle-database
MAXZZXAM
MAXZZXAM

Reputation: 67

how can I select XML attribute from oracle XMLTYPE column?

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?

Upvotes: 0

Views: 2333

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

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

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 2

Related Questions