Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XML Data. Extracting elements and attributes using Xpath or another?

chris001Feb 26 2013 — edited Feb 26 2013

Hi experts,
Using Oracle 11g.
I have an XML table which stores XML in one column (xml_col) like the below structure.
Example:

<measure id="abc">
  <data-elements>
    <data-element id="ab">
      <value>40</value>
    </data-element>
    <data-element id="cd">
      <value>8</value>
    </data-element>
    <data-element id="ef">
      <value>38</value>
    </data-element>
    <data-element id="gh">
      <value>32</value>
    </data-element>
  </data-elements>
</measure>

I've been trying endlessly to run XPath queries on this column to obtain the attribute of the <data-element> node and the value of the <value> node within.

My goal is to turn this into a table of:

ab | 40
cd | 8
ef | 38
gh | 32

My mind is stuck on doing this below and wrapping it with a CSV to rows hierarchical query. I can not convert to xmltype to a string to make that work though.

select str1,str2 from (
select extract(xml_col, 'string-join(//@id, '','')') str1
,extract(xml_col, 'string-join(//value, '','')') str2
from xml_temp_table)
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (str1, '[^,]+')) + 1;

But I get the following error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00607: Invalid reference: 'string-join'.

I'm looking for a non PL solution.

Any suggestions appreciated.
Thanks

Edited by: chris001 on Feb 26, 2013 12:06 PM

Edited by: chris001 on Feb 26, 2013 12:07 PM

This post has been answered by odie_63 on Feb 26 2013

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 26 2013

Added on Feb 26 2013

#11g, #database, #performance-availability, #query, #xml, #xml-db

2 comments

725 views