Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How to generate XPath string from XML file
New Roots
Member Posts: 184 Blue Ribbon
Hi,
Below is my XML format:
<project> <projectNumber>311927</projectNumber> <projectType>BUILD</projectType> <lineOfBusiness>COMMERCIAL</lineOfBusiness> <projectStatus>PROGRASS</projectStatus> <summary> <creationDate>08/02/2016</creationDate> <workflowStateDate></workflowStateDate> <effectiveDate>01/01/2014</effectiveDate> <clientRequested>FALSE</clientRequested> <mandatoryReview>FALSE</mandatoryReview> <internalProject>FALSE</internalProject> <clientType>Permanent</clientType> <description>Test Data 2</description> <appliesTo> <Retail>TRUE</Retail> <Mail>TRUE</Mail> </appliesTo> </summary> </project>
I'm loading above xml in one oracle configuration table and querying by using below query. As of now I'm passing "<span class="codeinlineitalic">XPath_string" manually.</span>
Is there any way to generate "<span class="codeinlineitalic">XPath_string" from xml file by using any inbuilt oracle function? That function should read my xml and should produce xpath string for each tag value.</span>
Eg output :
Tagvalue: Xpath
lineOfBusiness /project[1]/lineOfBusiness/text()
SELECT *FROM (SELECT 1924901 AS "KEY_IND_1" , 1924801 AS "KEY_IND_2" , EXTRACTVALUE(VALUE(P),'/project[1]/lineOfBusiness/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_3" , EXTRACTVALUE(VALUE(P),'/project[1]/projectNumber/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_4" , EXTRACTVALUE(VALUE(P),'/project[1]/projectStatus/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_5" , EXTRACTVALUE(VALUE(P),'/project[1]/projectType/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_6" FROM TABLE (XMLSEQUENCE( (SELECT NVL(xml_clob,'') FROM input_files WHERE extract_level = 1 ) )) P )WHERE key_ind_1 IS NOT NULLAND key_ind_2 IS NOT NULLAND key_ind_3 IS NOT NULLAND key_ind_4 IS NOT NULLAND key_ind_5 IS NOT NULLAND key_ind_6 IS NOT NULL;SELECT *FROM (SELECT 1925001 AS "KEY_IND_1" , (SELECT MAX(project_id) FROM XML_USER.project WHERE file_seq_id = 1924801 ) AS "KEY_IND_2" , 1924801 AS "KEY_IND_3" , EXTRACTVALUE(VALUE(P),'/summary[1]/clientRequested/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_4" , EXTRACTVALUE(VALUE(P),'/summary[1]/clientType/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_5" , EXTRACTVALUE(VALUE(P),'/summary[1]/creationDate/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_6" , EXTRACTVALUE(VALUE(P),'/summary[1]/description/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_7" , EXTRACTVALUE(VALUE(P),'/summary[1]/effectiveDate/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_8" , EXTRACTVALUE(VALUE(P),'/summary[1]/internalProject/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_9" , EXTRACTVALUE(VALUE(P),'/summary[1]/mandatoryReview/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_10" , EXTRACTVALUE(VALUE(P),'/summary[1]/workflowStateDate/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_11" , EXTRACTVALUE(VALUE(P),'/summary[1]/appliesTo/Retail/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_12" , EXTRACTVALUE(VALUE(P),'/summary[1]/appliesTo/Mail/text()', 'xmlns:"RXCONSTRUCT"') AS "KEY_IND_13" FROM TABLE (XMLSEQUENCE( (SELECT NVL(xml_clob,'') FROM input_files WHERE extract_level = 1 ) )) P )WHERE key_ind_1 IS NOT NULLAND key_ind_2 IS NOT NULLAND key_ind_3 IS NOT NULLAND key_ind_4 IS NOT NULLAND key_ind_5 IS NOT NULLAND key_ind_6 IS NOT NULLAND key_ind_7 IS NOT NULLAND key_ind_8 IS NOT NULLAND key_ind_9 IS NOT NULLAND key_ind_10 IS NOT NULLAND key_ind_11 IS NOT NULLAND key_ind_12 IS NOT NULLAND key_ind_13 IS NOT NULL;
Answers
-
There are various ways of querying XML data, though perhaps it's going a bit far to expect Oracle to parse your XML data and determine the Xpaths you need (some XML data may leave out optional elements). That is why we have XML Schemas to define what the XML should look like, and you can register XML Schemas with the database via XML DB, and use that, certainly to help improve performance when shredding an XML document (especially when you have large or lots of documents)
For your information, using EXTRACTVALUE and TABLE(XMLSEQUENCE... is deprecated functionality. Since 10g, there has been XMLTABLE and more recently XMLQUERY which can be used more effectively to extract data from XML (and it's easier to read).
I would suggest you take a look over in the XML DB space which is dedicated to dealing with XML in the database.
-
Example of using XMLTABLE...
SQL> ed
Wrote file afiedt.buf 1 with t(xml) as (select xmltype('
2 <projects xmlns="http://www.oracle.com/somedefaultnamespace">
3 <project>
4 <projectNumber>311927</projectNumber>
5 <projectType>BUILD</projectType>
6 <lineOfBusiness>COMMERCIAL</lineOfBusiness>
7 <projectStatus>PROGRASS</projectStatus>
8 <summary>
9 <creationDate>08/02/2016</creationDate>
10 <workflowStateDate></workflowStateDate>
11 <effectiveDate>01/01/2014</effectiveDate>
12 <clientRequested>FALSE</clientRequested>
13 <mandatoryReview>FALSE</mandatoryReview>
14 <internalProject>FALSE</internalProject>
15 <clientType>Permanent</clientType>
16 <description>Test Data 2</description>
17 <appliesTo>
18 <Retail>TRUE</Retail>
19 <Mail>TRUE</Mail>
20 </appliesTo>
21 </summary>
22 </project>
23 <project>
24 <projectNumber>311928</projectNumber>
25 <projectType>BUILD</projectType>
26 <lineOfBusiness>INTERNAL</lineOfBusiness>
27 <projectStatus>ON HOLD</projectStatus>
28 <summary>
29 <creationDate>01/06/2016</creationDate>
30 <workflowStateDate></workflowStateDate>
31 <effectiveDate>01/01/2015</effectiveDate>
32 <clientRequested>FALSE</clientRequested>
33 <mandatoryReview>FALSE</mandatoryReview>
34 <internalProject>TRUE</internalProject>
35 <clientType>Temporary</clientType>
36 <description>Test Data 3</description>
37 <appliesTo>
38 <Retail>FALSE</Retail>
39 <Mail>TRUE</Mail>
40 </appliesTo>
41 </summary>
42 </project>
43 </projects>') from dual)
44 --
45 -- end of test data
46 --
47 select x.*
48 from t
49 ,xmltable(xmlnamespaces(default 'http://www.oracle.com/somedefaultnamespace')
50 ,'/projects/project'
51 passing t.xml
52 columns r for ordinality
53 ,projectNumber number path './projectNumber'
54 ,projectType varchar2(10) path './projectType'
55 ,lineOfBusiness varchar2(10) path './lineOfBusiness'
56 ,projectStatus varchar2(10) path './projectStatus'
57 ,creationDate varchar2(10) path './summary/creationDate'
58 ,workflowStateDate varchar2(10) path './summary/worlflowStateDate'
59 ,effectiveDate varchar2(10) path './summary/effectiveDate'
60 ,clientReq varchar2(5) path './summary/clientRequested'
61 ,mandatoryReview varchar2(5) path './summary/mandatoryReview'
62 ,internalProject varchar2(5) path './summary/internalProject'
63 ,clientType varchar2(10) path './summary/clientType'
64 ,description varchar2(20) path './summary/description'
65 ,appliesToRetail varchar2(5) path './summary/appliesTo/Retail'
66 ,appliesToMail varchar2(5) path './summary/appliesTo/Mail'
67* ) x
SQL> / R PROJECTNUMBER PROJECTTYP LINEOFBUSI PROJECTSTA CREATIONDA WORKFLOWST EFFECTIVED CLIEN MANDA INTER CLIENTTYPE DESCRIPTION APPLI APPLI
---------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ----- ----- ----- ---------- -------------------- ----- -----
1 311927 BUILD COMMERCIAL PROGRASS 08/02/2016 01/01/2014 FALSE FALSE FALSE Permanent Test Data 2 TRUE TRUE
2 311928 BUILD INTERNAL ON HOLD 01/06/2016 01/01/2015 FALSE FALSE TRUE Temporary Test Data 3 FALSE TRUE -
Is there any way to generate "
<span class="codeinlineitalic">XPath_string" from xml file by using any inbuilt oracle function? That function should read my xml and should produce xpath string for each tag value.</span>Eg output : Tagvalue: XpathlineOfBusiness /project[1]/lineOfBusiness/text()Yes, that's possible. But why would you need it?
As BluShadow said, use XMLTABLE with static XQuery expressions. That's the best way to query XML documents.
-
Hi Odie,
Yes .I will have to look at BluShadow's suggestion. Can you please share the function which extracts the xpath string from xml file? It could be helpful for me.
-
Hi Blueshadow,
Thanks for sharing the sample code. It sis very helpful . Is there any way to construct path automatically?
-
Can you please share the function which extracts the xpath string from xml file?
There's no documented function for that - probably because it's not a good idea
From an academic standpoint, here are a couple of ways to do it :
create table tmp_xml of xmltype;insert into tmp_xml values (xmltype('<project> <projectNumber>311927</projectNumber> <projectType>BUILD</projectType> <lineOfBusiness>COMMERCIAL</lineOfBusiness> <projectStatus>PROGRASS</projectStatus> <summary> <creationDate>08/02/2016</creationDate> <workflowStateDate></workflowStateDate> <effectiveDate>01/01/2014</effectiveDate> <clientRequested>FALSE</clientRequested> <mandatoryReview>FALSE</mandatoryReview> <internalProject>FALSE</internalProject> <clientType>Permanent</clientType> <description>Test Data 2</description> <appliesTo> <Retail>TRUE</Retail> <Mail>TRUE</Mail> </appliesTo> </summary> </project>'));1) Using XQuery only :
SQL> select x.node_id 2 , x.node_name 3 , x.node_type 4 , x.node_xpath 5 from tmp_xml t 6 , xmltable( 7 'declare function local:getChildren($e as node(), $pid as xs:string?, $pxp as xs:string?) as element()* 8 { 9 for $i at $p in $e/(node()|@*) 10 let $id := if ($pid) then concat($pid,".",$p) else "1" 11 let $xp := concat($pxp, "/", 12 typeswitch($i) 13 case text() return "text()" 14 case attribute() return concat("@, local-name($i)) 15 case element() return concat(local-name($i), [", count($i/preceding-sibling::*[name(.)=name($i)])+1, "]") 16 default return "" 17 ) 18 return element r 19 { 20 element node_id {$id} 21 , element parent_node_id {$pid} 22 , element node_name {local-name($i)} 23 , element node_value { if (not($i instance of element())) then data($i) else () } 24 , element node_type { 25 typeswitch($i) 26 case text() return "text" 27 case attribute() return "attribute" 28 case element() return "element" 29 case processing-instruction() return "processing-instruction" 30 case comment() return "comment" 31 default return "other" 32 } 33 , element namespace_uri {namespace-uri($i)} 34 , element node_xpath {$xp} 35 } 36 | local:getChildren($i, $id, $xp) 37 }; (:
38 local:getChildren($d,(),())' 39 passing t.object_value as "d" 40 columns node_id varchar2(100) path 'node_id' 41 , node_name varchar2(2000) path 'node_name' 42 , node_value varchar2(4000) path 'node_value' 43 , parent_node_id varchar2(100) path 'parent_node_id' 44 , node_type varchar2(30) path 'node_type' 45 , namespace_uri varchar2(2000) path 'namespace_uri' 46 , node_xpath varchar2(4000) path 'node_xpath' 47 ) x 48 ;NODE_ID NODE_NAME NODE_TYPE NODE_XPATH----------- ------------------ ---------- ----------------------------------------------------1.5.1 creationDate element /project[1]/summary[1]/creationDate[1]1.5 summary element /project[1]/summary[1]1.4.1 text /project[1]/projectStatus[1]/text()1.4 projectStatus element /project[1]/projectStatus[1]1.3.1 text /project[1]/lineOfBusiness[1]/text()1.3 lineOfBusiness element /project[1]/lineOfBusiness[1]1.2.1 text /project[1]/projectType[1]/text()1.5.9.2.1 text /project[1]/summary[1]/appliesTo[1]/Mail[1]/text()1.5.9.2 Mail element /project[1]/summary[1]/appliesTo[1]/Mail[1]1.5.9.1.1 text /project[1]/summary[1]/appliesTo[1]/Retail[1]/text()1.5.9.1 Retail element /project[1]/summary[1]/appliesTo[1]/Retail[1]1.5.9 appliesTo element /project[1]/summary[1]/appliesTo[1]1.5.8.1 text /project[1]/summary[1]/description[1]/text()1.5.8 description element /project[1]/summary[1]/description[1]1.1 projectNumber element /project[1]/projectNumber[1]1 project element /project[1]1.5.7.1 text /project[1]/summary[1]/clientType[1]/text()1.5.7 clientType element /project[1]/summary[1]/clientType[1]1.5.6.1 text /project[1]/summary[1]/internalProject[1]/text()1.5.6 internalProject element /project[1]/summary[1]/internalProject[1]1.5.5.1 text /project[1]/summary[1]/mandatoryReview[1]/text()1.5.5 mandatoryReview element /project[1]/summary[1]/mandatoryReview[1]1.5.4.1 text /project[1]/summary[1]/clientRequested[1]/text()1.5.4 clientRequested element /project[1]/summary[1]/clientRequested[1]1.5.3.1 text /project[1]/summary[1]/effectiveDate[1]/text()1.5.3 effectiveDate element /project[1]/summary[1]/effectiveDate[1]1.5.2 workflowStateDate element /project[1]/summary[1]/workflowStateDate[1]1.5.1.1 text /project[1]/summary[1]/creationDate[1]/text()1.2 projectType element /project[1]/projectType[1]1.1.1 text /project[1]/projectNumber[1]/text()30 rows selected.2) Using XMLFlattenDOM function :
SQL> with edge_data as ( 2 select x.* 3 , row_number() over(partition by parent_node_id, node_name order by node_id) as position 4 from tmp_xml t 5 , table(xmlflattendom(t.object_value)) x 6 ) 7 select t.node_id 8 , t.node_name 9 , sys_connect_by_path( 10 case node_type 11 when 'element' then node_name || '[' || position || ']' 12 when 'attribute' then '@' || node_name 13 else 'text()' 14 end 15 , '/' 16 ) as xpath 17 from edge_data t 18 where node_type in ('element', 'attribute', 'text') 19 connect by prior node_id = parent_node_id 20 start with parent_node_id is null ; NODE_ID NODE_NAME XPATH---------- ------------------ ---------------------------------------------------- 1 project /project[1] 2 projectNumber /project[1]/projectNumber[1] 3 /project[1]/projectNumber[1]/text() 4 projectType /project[1]/projectType[1] 5 /project[1]/projectType[1]/text() 6 lineOfBusiness /project[1]/lineOfBusiness[1] 7 /project[1]/lineOfBusiness[1]/text() 8 projectStatus /project[1]/projectStatus[1] 9 /project[1]/projectStatus[1]/text() 10 summary /project[1]/summary[1] 11 creationDate /project[1]/summary[1]/creationDate[1] 12 /project[1]/summary[1]/creationDate[1]/text() 13 workflowStateDate /project[1]/summary[1]/workflowStateDate[1] 14 effectiveDate /project[1]/summary[1]/effectiveDate[1] 15 /project[1]/summary[1]/effectiveDate[1]/text() 16 clientRequested /project[1]/summary[1]/clientRequested[1] 17 /project[1]/summary[1]/clientRequested[1]/text() 18 mandatoryReview /project[1]/summary[1]/mandatoryReview[1] 19 /project[1]/summary[1]/mandatoryReview[1]/text() 20 internalProject /project[1]/summary[1]/internalProject[1] 21 /project[1]/summary[1]/internalProject[1]/text() 22 clientType /project[1]/summary[1]/clientType[1] 23 /project[1]/summary[1]/clientType[1]/text() 24 description /project[1]/summary[1]/description[1] 25 /project[1]/summary[1]/description[1]/text() 26 appliesTo /project[1]/summary[1]/appliesTo[1] 27 Retail /project[1]/summary[1]/appliesTo[1]/Retail[1] 28 /project[1]/summary[1]/appliesTo[1]/Retail[1]/text() 29 Mail /project[1]/summary[1]/appliesTo[1]/Mail[1] 30 /project[1]/summary[1]/appliesTo[1]/Mail[1]/text()30 rows selected.
-
Hi Odie_63,
Great. Thank you very much for example.
-
Hi @odie_63
When I execute your first method below error message is raised. Please help me to resolve.
ORA-19112: error raised during evaluation:
XVM-01004: [XPTY0004] Expression type does not match a required type
19112. 00000 - "error raised during evaluation: %s"
*Cause: The error function was called during evaluation of the XQuery expression.
*Action: Check the detailed error message for the possible causes.
-
What's your database version?
-
Hi Blushadow,
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
This discussion has been closed.