0

I am looking for Oracle query which can be run on a table that has one of the column storing XML's This query should dynamically take the xpath in the query to extract the string from all nodes. I have tried below but i have to hardcode the xpath. I do not want to hardcode the xpath.

Is there a way to generate the xpath, based on the structure of XML(structure may vary) on the fly to extract the xml node value?

Output i need is the extracted value of the xml node.

I tried:

SELECT xmltype(XMLCOLUMN).EXTRACT('/report/fulcriteria/text()'),
       xmltype(XMLCOLUMN).EXTRACT('/report/conumb/text()'),
       xmltype(XMLCOLUMN).EXTRACT('/report/dup/text()'),
       xmltype(XMLCOLUMN).EXTRACT('/report/reportdup[1]/dupsource/text()'),
       xmltype(XMLCOLUMN).EXTRACT('/report/reportdup[2]/dupsource/text()'),
       xmltype(XMLCOLUMN).EXTRACT('/report/reportdup[2]/dupnumb/text()'),
       xmltype(XMLCOLUMN).EXTRACT('/report/reportdup[3]/dupsource/text()'),
       xmltype(XMLCOLUMN).EXTRACT('/report/reportdup[3]/dupnumb/text()') . 
       xmltype(XMLCOLUMN).EXTRACT('/report/final/dup/text()'),
  FROM MYTABLE
Improve this question
5
  • "This query should dynamically take the xpath in the query to extract the string from all nodes" How are you providing this dynamic path? Is it in another table? Is it being passed in a bind variable? Something else? Please edit your question with a minimal reproducible example that includes all the details of how you are generating the query. Commented Dec 25, 2019 at 21:35
  • @MT0: Let me clarify, I have one table MYTABLE.XMLCOLUMN which store the XML Files. I am not storing xpath in any table. The XML follow some structure and has repeated blocks in it. As i know the structure so i am hardcoding the xpaths. Do i need a table to store the XPATH ? Commented Dec 25, 2019 at 22:24
  • You say that "query should dynamically take the xpath"; if it is dynamic then you need to pass the path from somewhere (tell us where and how). If it is not dynamic then you can use static paths (and I'm not sure what the point of this question would be). Whatever you chose you need to tell us what your specifications are as we can guess and make stuff up but its probably not going to be exactly what you intend. So why don't you work out exactly what you want and edit your question with a minimal reproducible example that tells us what that is. Commented Dec 25, 2019 at 22:58
  • If you just want to list all paths and corresponding values in an XML document then this is a duplicate of stackoverflow.com/q/49039401/1509264 Commented Dec 25, 2019 at 23:11
  • @MT0 : From that Post , i am getting error at line "PASSING d.xml AS "doc" saying "d.xml is invalid identifier" Not sure why as xml is the column of XMLTYPE. Commented Dec 26, 2019 at 0:05

1 Answer 1

Reset to default
2

Join the table containing your dynamic paths with the table containing the XML and then use XMLTABLE to extract the value:

Oracle Setup:

CREATE TABLE mytable ( id, xmlcolumn ) AS
SELECT 1, '<report>
  <fulcriteria>1</fulcriteria>
  <conumb>2</conumb>
  <dup>3</dup>
  <reportdup><dupsource>4.1</dupsource></reportdup>
  <reportdup><dupsource>4.2a</dupsource><dupnumb>4.2b</dupnumb></reportdup>
  <reportdup><dupsource>4.3a</dupsource><dupnumb>4.3b</dupnumb></reportdup>
  <final><dup>5</dup></final>
</report>' FROM DUAL;

CREATE TABLE paths ( path ) AS
SELECT '/report/fulcriteria' FROM DUAL UNION ALL
SELECT '/report/conumb' FROM DUAL UNION ALL
SELECT '/report/dup' FROM DUAL UNION ALL
SELECT '/report/reportdup[1]/dupsource' FROM DUAL UNION ALL
SELECT '/report/reportdup[2]/dupsource' FROM DUAL UNION ALL
SELECT '/report/reportdup[2]/dupnumb' FROM DUAL UNION ALL
SELECT '/report/reportdup[3]/dupsource' FROM DUAL UNION ALL
SELECT '/report/reportdup[3]/dupnumb'  FROM DUAL UNION ALL
SELECT '/report/final/dup' FROM DUAL;

Query 1:

SELECT t.id,
       p.path,
       x.value
FROM   MYTABLE t
       CROSS JOIN PATHS p
       CROSS JOIN XMLTABLE(
         p.path
         PASSING XMLTYPE( t.xmlcolumn )
         COLUMNS
           value VARCHAR2(4000) PATH '.'
       ) x

Query 2:

SELECT t.id,
       p.path,
       XMLQUERY(
         (p.path || '/text()')
         PASSING XMLTYPE( t.xmlcolumn )
         RETURNING CONTENT
       ) AS value
FROM   MYTABLE t
       CROSS JOIN PATHS p;

Query 3:

SELECT t.id,
       p.path,
       XMLTYPE( t.xmlcolumn ).EXTRACT(p.path||'/text()').getStringVal() AS value
FROM   MYTABLE t
       CROSS JOIN PATHS p;

Output:

ID | PATH                           | VALUE
-: | :----------------------------- | :----
 1 | /report/fulcriteria            | 1    
 1 | /report/conumb                 | 2    
 1 | /report/dup                    | 3    
 1 | /report/reportdup[1]/dupsource | 4.1  
 1 | /report/reportdup[2]/dupsource | 4.2a 
 1 | /report/reportdup[2]/dupnumb   | 4.2b 
 1 | /report/reportdup[3]/dupsource | 4.3a 
 1 | /report/reportdup[3]/dupnumb   | 4.3b 
 1 | /report/final/dup              | 5

db<>fiddle here

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

3 Comments

Alas, I couldn't make it work with a default namespace. db<>fiddle here
@T-Gergely I couldn't immediately figure out the namespace issue with XMLTABLE but there are working versions using XMLQUERY and (the deprecated) EXTRACT functions in this db<>fiddle.
Thanks for the tips. I have a more complicated query, and only XMLTYPE.EXTRACT member function (that may not be deprecated) works and only with the NO_XML_QUERY_REWRITE hint. XMLQUERY and the deprecated EXTRACT, EXTRACTVALUE all failed and not even consistently: no rows, null values, ORA-600 [qmxqrsWriteItemToOpn:itemTyp] and other errors. (12.1 Enterprise Edition with not too old patches.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.