Extract XML in a Oracle Database Table Column

Question

I have a table 'Users' with column 'Values' in a Oracle database which has XML data like so:

<Attributes>
  <Map>
    <entry key="first" value="Linda"/>
    <entry key="groups" value="Manager"/>
    <entry key="last" value="Davis"/>
    <entry key="locked" value="N"/>
    <entry key="status" value="A"/>
  </Map>
</Attributes>

I wish to extract them like:

Attributes_Values
=================
first = Linda
groups = Manager
last = Davis

I tried ExtractValue function but it returns error saying 'missing right parenthesis'. My query below:

SELECT EXTRACTVALUE(Values, '/Attributes/Map/entry[@key='first']@value') "Attribute_Values" from 
Users;

I3rutt · Accepted Answer · 2017-09-11 11:26:36Z

1

SQL:

with t as (
select xmltype('<Attributes>
  <Map>
    <entry key="first" value="Linda"/>
    <entry key="groups" value="Manager"/>
    <entry key="last" value="Davis"/>
    <entry key="locked" value="N"/>
    <entry key="status" value="A"/>
  </Map>
</Attributes>') xml from dual
)

select dtl.extract('//@key').getStringVal() || ' = ' || dtl.extract('//@value').getStringVal() as "Attributes_Values"
from t s
    ,table(XMLSequence(s.xml.extract('Attributes/Map/entry'))) dtl

Result:

Attributes_Values
first = Linda
groups = Manager
last = Davis
locked = N
status = A

answered Sep 11, 2017 at 11:26

I3rutt

6025 silver badges19 bronze badges

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

Comments

Arkadiusz Łukasiewicz · Accepted Answer · 2017-09-11 10:38:25Z

1

Quotation mark instead of the apostrophe.

SELECT EXTRACTVALUE(xmltype('<Attributes>
  <Map>
    <entry key="first" value="Linda"/>
    <entry key="groups" value="Manager"/>
    <entry key="last" value="Davis"/>
    <entry key="locked" value="N"/>
    <entry key="status" value="A"/>
  </Map>
</Attributes>'), '/Attributes/Map/entry[@key="first"]/@value') "Attribute_Values" from dual;

answered Sep 11, 2017 at 10:38

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

2 Comments

Gaurav Kabra Over a year ago

Works fine! Was wondering if we can extract multiple values by passing their relevant key identifiers. E.g. @key="first", @key"last".

Arkadiusz Łukasiewicz Over a year ago

Yes but not with extractvalue. I'thik extractvalue is deprecatd.

SELECT * from xmltable( '/Attributes/Map/entry[@key="first" or @key="last" ]/@value' passing xmltype('<Attributes>   <Map>     <entry key="first" value="Linda"/>     <entry key="groups" value="Manager"/>     <entry key="last" value="Davis"/>     <entry key="locked" value="N"/>     <entry key="status" value="A"/>   </Map> </Attributes>') columns value varchar(200) path '.' )

Collectives™ on Stack Overflow

Extract XML in a Oracle Database Table Column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related