Parsing XML in Oracle

Question

I have an interesting situaiton in oracle. We store an xml string in a field and part of as follows:

<dc:subject>info1</dc:subject>  <dc:subject>info2</dc:subject><dc:subject>info3</dc:subject>

And when i am querying this field, I use the following:

m.M_DC.extract('/qualifieddc/dc:subject/text()', 'xmlns:dc="http://purl.org/dc/elements/1.1/"').getStringVal() "INFO",

And the resultant INFO area becomes "info1info2info3".

Is there any oracle function or a way to make this info area as "info1, info2, info3" ?

Any help would be appreciated...

Thanks...

Samuel · Accepted Answer · 2011-01-13 15:22:35Z

2

XML in Oracle is a nightmare. You can try something like this (XMLTABLE to the rescue):

 > select wmsys.wm_concat(''||column_value) text
  2  from
  3  xmltable(
  4     XMLNAMESPACES('http://purl.org/dc/elements/1.1/' as "dc"),
  5     '$xml/qualifieddc/dc:subject/text()'
  6     passing xmltype('<qualifieddc xmlns:dc="http://purl.org/dc/elements/1.1/
">' ||
  7             '<dc:subject>info1</dc:subject>  <dc:subject>info2</dc:subject><
dc:subject>info3</dc:subject>' ||
  8         '</qualifieddc>') as "xml");

TEXT
--------------------------------------------------------------------------------

info1,info2,info3

Note that I'm using wmsys.wm_concat for concatenation, which is not recommended. Use any other stragg implementation.

answered Jan 13, 2011 at 15:22

Samuel

2,5002 gold badges19 silver badges21 bronze badges

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

Comments

JOTN · Accepted Answer · 2011-01-13 14:44:09Z

0

I think that would be a transform rather than a query. Are you familiar with XSL? If so, the look at the XMLTransform() examples, and it should be pretty easy from there:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions254.htm#SQLRF06171

answered Jan 13, 2011 at 14:44

JOTN

6,4022 gold badges31 silver badges33 bronze badges

Comments

Mohit Tamrakar · Accepted Answer · 2014-12-01 04:46:19Z

-1

This code will help you.

DECLARE x XMLType := XMLType( ' info1
info2 info3'); BEGIN FOR r IN ( SELECT ExtractValue(Value(p),'/dc/text()') as name FROM TABLE(XMLSequence(Extract(x,'/dc'))) p ) LOOP

END LOOP; END;

code tested by me in plsql developer.

answered Dec 1, 2014 at 4:46

Mohit Tamrakar

4051 gold badge5 silver badges7 bronze badges

Collectives™ on Stack Overflow

Parsing XML in Oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related