Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
get path to nodes by value
Answers
-
And what it's return in your case??
-
Gastons21 wrote:not so sure what you mean by that. i think you asked me to show the result after executing...
And what it's return in your case??SQL> ed Wrote file afiedt.buf 1 WITH tab AS 2 (SELECT xmltype('<?xml version="1.0"?> 3 <purchase_order> 4 <po_items> 5 <quantity>#number#</quantity> 6 <item> 7 <price>#Price#</price> 8 <name>#Name#</name> 9 <quantity>#number#</quantity> 10 </item> 11 </po_items> 12 </purchase_order>') col 13 FROM dual 14 ) 15 SELECT nodepath,nodevalue 16 FROM tab t, 17 xmltable(' 18 for $i in $tmp/descendant::* 19 where $i/text() != "" 20 return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R> 21 ' passing t.col AS "tmp" columns 22 nodepath varchar2(50) path '//P', 23* nodevalue varchar2(50) path '//V') SQL> / NODEPATH NODEVALUE -------------------------------------------------- ------------------------------------------------- purchase_order/po_items/quantity #number# purchase_order/po_items/item/price #Price# purchase_order/po_items/item/name #Name# purchase_order/po_items/item/quantity #number#
Ravi Kumar -
Works like I need, but with one difference. There select all xml elements where is text.
But I need to select path and #variables# only!
for example:
with your code I get in result:office.text/text:p L?muma Nr.#numurs#
but I need:office.text/text:p #numurs#
and places where not this #varaible_name# don't select into table!
Thanks for help!
Edited by: Gastons21 on Feb 19, 2010 2:30 AM -
if i understand you properly...you have a xml..and inside it you want to get a all node paths for a particular text.
SQL> ed Wrote file afiedt.buf 1 WITH tab AS 2 (SELECT xmltype('<?xml version="1.0"?> 3 <purchase_order> 4 <po_items> 5 <quantity>#number#</quantity> 6 <item> 7 <price>#Price#</price> 8 <name>#Name#</name> 9 <quantity>#number#</quantity> 10 </item> 11 </po_items> 12 </purchase_order>') col 13 FROM dual 14 ) 15 SELECT nodepath,nodevalue 16 FROM tab t, 17 xmltable(' 18 for $i in $tmp/descendant::* 19 where $i/text() = "#number#" 20 return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R> 21 ' passing t.col AS "tmp" columns 22 nodepath varchar2(50) path '//P', 23* nodevalue varchar2(50) path '//V') SQL> / NODEPATH NODEVALUE -------------------------------------------------- ------------------------------------------------- purchase_order/po_items/quantity #number# purchase_order/po_items/item/quantity #number#
check for below line especially to be set...where $i/text() = "#number#"
i shown an example for getting a nodepath for nodevalue #number# for the sample xml
Ravi Kumar -
xmltype('<?xml version="1.0"?> <purchase_order> <po_items> <quantity>NUmber of Number: #number#</quantity> <item> <price>Unit price: #Price#</price> <name> Unit name#Name#</name> <quantity>Unit qant: #number#</quantity> </item> </po_items> </purchase_order>') col
there is xml where nodes text ar:
NUmber of Number: #number#
Unit price: #Price#
Unit name#Name#
Unit qant: #number#
but I need get path of these nodes and only #variable_name# values. For example:PATH VALUE -------------------------------------- ----------- purchase_order/po_items/quantity #number# purchase_order/po_items/item/quantity #Price# purchase_order/po_items/item/quantity #Name# purchase_order/po_items/item/quantity #number#
I hope you understand me now wrightAnd then all these values put in table where I store records
Edited by: Gastons21 on Feb 19, 2010 3:20 AM
Edited by: Gastons21 on Feb 19, 2010 3:21 AM -
WITH tab AS (SELECT xmltype('<?xml version="1.0"?> <purchase_order> <po_items> <quantity>NUmber of Number: #number#</quantity> <item> <price>Unit price: #Price#</price> <name> Unit name#Name#</name> <quantity>Unit qant: #number#</quantity> </item> </po_items> </purchase_order>') col FROM dual ) SELECT nodepath,regexp_substr(nodevalue,'#.*#') nodevalue FROM tab t, xmltable(' for $i in $tmp/descendant::* where $i/text() != "" return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R> ' passing t.col AS "tmp" columns nodepath varchar2(50) path '//P', nodevalue varchar2(50) path '//V')
just use a simple regular expression after getting data like above.regexp_substr(nodevalue,'#.*#')
Scrapping the data is difficult after that we can use our normal oracle functions to modify the data in the manner you want. ;-)
Ravi Kumar -
Ok..but there is output in xml, but how can I read datas into table of records??
-
Gastons21 wrote:do you mean putting above selected data in a table??
Ok..but there is output in xml, but how can I read datas into table of records??SQL> create table test(nodepath varchar2(40),nodetext varchar2(40)); Table created. SQL> ed Wrote file afiedt.buf 1 insert into test 2 WITH tab AS 3 (SELECT xmltype('<?xml version="1.0"?> 4 <purchase_order> 5 <po_items> 6 <quantity>NUmber of Number: #number#</quantity> 7 <item> 8 <price>Unit price: #Price#</price> 9 <name> Unit name#Name#</name> 10 <quantity>Unit qant: #number#</quantity> 11 </item> 12 </po_items> 13 </purchase_order>') col 14 FROM dual 15 ) 16 SELECT nodepath,regexp_substr(nodevalue,'#.*#') nodevalue 17 FROM tab t, 18 xmltable(' 19 for $i in $tmp/descendant::* 20 where $i/text() != "" 21 return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R> 22 ' passing t.col AS "tmp" columns 23 nodepath varchar2(50) path '//P', 24* nodevalue varchar2(50) path '//V') SQL> / 4 rows created. SQL> select * from test; NODEPATH NODETEXT ---------------------------------------- ---------------------------------------- purchase_order/po_items/quantity #number# purchase_order/po_items/item/price #Price# purchase_order/po_items/item/name #Name# purchase_order/po_items/item/quantity #number# SQL> drop table test; Table dropped. SQL>
Ravi Kumar -
Please can you help me little bit more
how can I put these values into table of record?
Theres code:this is code in package: TYPE Var_Names IS RECORD ( v_name VARCHAR2(100), XPath VARCHAR2(500) ); TYPE Var_Name_List IS TABLE OF Var_Names; Variables Var_Name_List; this is code in body package: FUNCTION selectVariablesInList(tempXML XMLType) RETURN Var_Name_List IS BEGIN WITH tab AS (SELECT tempXML col FROM dual ) SELECT nodepath, regexp_substr(nodevalue,'#.*#') nodevalue INTO Variables FROM tab t, xmltable(' for $i in $tmp/descendant::* where $i/text() != "" return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>' passing t.col AS "tmp" columns nodepath varchar2(50) path '//P', nodevalue varchar2(50) path '//V'); RETURN Variables; END;
-
you need to use sql types not pl/sql records for using inside sql queries within functions...check this it will be useful....replace the code as required for you...
SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE type type_node 2 AS 3 object 4 ( 5 nodepath VARCHAR2(100), 6* nodevalue VARCHAR2(500) ); SQL> / Type created. SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE type type_node_tab 2 AS 3* TABLE OF type_node; SQL> / Type created. SQL> ed Wrote file afiedt.buf 1 create or replace 2 PACKAGE test_ravi AS 3 function selectVariablesInList (tempXML XMLType) RETURN type_node_tab; 4* END test_ravi; SQL> / Package created. SQL> ed Wrote file afiedt.buf 1 create or replace 2 PACKAGE BODY test_ravi AS 3 FUNCTION selectVariablesInList(tempXML XMLType) RETURN type_node_tab 4 IS 5 v_type_node_tab type_node_tab; 6 BEGIN 7 select cast(multiset( 8 select nodepath,nodevalue from ( 9 WITH tab AS 10 (SELECT tempXML col 11 FROM dual 12 ) 13 SELECT nodepath,regexp_substr(nodevalue,'#.*#') nodevalue 14 FROM tab t, 15 xmltable(' 16 for $i in $tmp/descendant::* 17 where $i/text() != "" 18 return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>' 19 passing t.col AS "tmp" columns 20 nodepath VARCHAR2(100) path '//P', 21 nodevalue VARCHAR2(500) path '//V')) 22 ) as type_node_tab) into v_type_node_tab from dual; 23 RETURN v_type_node_tab; 24 END; 25* END test_ravi; SQL> / Package body created. SQL> ed Wrote file afiedt.buf 1 select * from table(test_ravi.selectvariablesinlist(xmltype('<?xml version="1.0"?> 2 <purchase_order> 3 <po_items> 4 <quantity>NUmber of Number: #number#</quantity> 5 <item> 6 <price>Unit price: #Price#</price> 7 <name> Unit name#Name#</name> 8 <quantity>Unit qant: #number#</quantity> 9 </item> 10 </po_items> 11* </purchase_order>'))) SQL> / NODEPATH ---------------------------------------------------------------------------------------------------- NODEVALUE ---------------------------------------------------------------------------------------------------- purchase_order/po_items/quantity #number# purchase_order/po_items/item/price #Price# purchase_order/po_items/item/name #Name# NODEPATH ---------------------------------------------------------------------------------------------------- NODEVALUE ---------------------------------------------------------------------------------------------------- purchase_order/po_items/item/quantity #number# SQL>
Ravi Kumar
This discussion has been closed.