Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K 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
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K 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)
- 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
- 437 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

754085
Member Posts: 45
Can enybody please help me. I don't know really how to get path from xml
for example, this is xml:
<?xml version="1.0"?>
<purchase_order>
<po_items>
<item>
<name>#Name#</name>
<quantity>#number#</quantity>
</item>
</po_items>
</purchase_order>
and get out path by variebles #Name# and #number#.
In this case i need to get result:
purchase_order/po_items/item/name
and
purchase_order/po_items/item/quantity
for example, this is xml:
<?xml version="1.0"?>
<purchase_order>
<po_items>
<item>
<name>#Name#</name>
<quantity>#number#</quantity>
</item>
</po_items>
</purchase_order>
and get out path by variebles #Name# and #number#.
In this case i need to get result:
purchase_order/po_items/item/name
and
purchase_order/po_items/item/quantity
Best Answer
-
If you have multiple item tags:
SQL> with t as ( 2 select xmltype('<?xml version="1.0"?> 3 <purchase_order> 4 <po_items> 5 <item> 6 <name>#Name#</name> 7 <quantity>#number#</quantity> 8 </item> 9 <item> 10 <name>#Name2#</name> 11 <quantity>#number2#</quantity> 12 </item> 13 <item> 14 <name>#Name3#</name> 15 <quantity>#number3#</quantity> 16 </item> 17 </po_items> 18 </purchase_order>') xml from dual) 19 select x.* 20 from t, xmltable('/purchase_order/po_items/item' PASSING t.xml COLUMNS 21 name varchar2(20) PATH '/item/name', 22 qty varchar2(20) PATH '/item/quantity') x; NAME QTY -------------------- -------------------- #Name# #number# #Name2# #number2# #Name3# #number3#
Max
http://oracleitalia.wordpress.com
Answers
-
SQL> with t as ( 2 select xmltype('<?xml version="1.0"?> 3 <purchase_order> 4 <po_items> 5 <item> 6 <name>#Name#</name> 7 <quantity>#number#</quantity> 8 </item> 9 </po_items> 10 </purchase_order>') xml from dual) 11 select x.* 12 from t, xmltable('/' PASSING t.xml COLUMNS 13 name varchar2(20) PATH '/purchase_order/po_items/item/name', 14 qty varchar2(20) PATH '/purchase_order/po_items/item/quantity') x; NAME QTY -------------------- -------------------- #Name# #number#
Max
http://oracleitalia.wordpress.com -
If you have multiple item tags:
SQL> with t as ( 2 select xmltype('<?xml version="1.0"?> 3 <purchase_order> 4 <po_items> 5 <item> 6 <name>#Name#</name> 7 <quantity>#number#</quantity> 8 </item> 9 <item> 10 <name>#Name2#</name> 11 <quantity>#number2#</quantity> 12 </item> 13 <item> 14 <name>#Name3#</name> 15 <quantity>#number3#</quantity> 16 </item> 17 </po_items> 18 </purchase_order>') xml from dual) 19 select x.* 20 from t, xmltable('/purchase_order/po_items/item' PASSING t.xml COLUMNS 21 name varchar2(20) PATH '/item/name', 22 qty varchar2(20) PATH '/item/quantity') x; NAME QTY -------------------- -------------------- #Name# #number# #Name2# #number2# #Name3# #number3#
Max
http://oracleitalia.wordpress.com -
I'm not think this to get in result:
NAME QTY ---------------- ----------------- #Name# #number#
in my result needsVALUE PATH -------------- ------------------------------ #Name# /purchase_order/po_items/item/name #number# /purchase_order/po_items/item/quantity
-
Massimo Ruocchio do you have some ideas??
-
In Oracle 10g:
SQL> with t as ( 2 select xmltype('<?xml version="1.0"?> 3 <purchase_order> 4 <po_items> 5 <item> 6 <name>#Name#</name> 7 <quantity>#number#</quantity> 8 </item> 9 </po_items> 10 </purchase_order>') xml from dual) 11 select x.val, 12 '/purchase_order/po_items/item/'||xmlquery('for $i in /* return name($i)' 13 PASSING x.col RETURNING CONTENT) path 14 from t, xmltable('/purchase_order/po_items/item/*' PASSING t.xml COLUMNS 15 val varchar2(20) PATH '.', 16 col xmltype PATH '.') x; VAL PATH -------------------- -------------------------------------------------- #Name# /purchase_order/po_items/item/name #number# /purchase_order/po_items/item/quantity
In Oracle11g the following should work:with t as ( select xmltype('<?xml version="1.0"?> <purchase_order> <po_items> <item> <name>#Name#</name> <quantity>#number#</quantity> </item> </po_items> </purchase_order>') xml from dual) select x.val, '/purchase_order/po_items/item/'||x.col path from t, xmltable('/purchase_order/po_items/item/*' PASSING t.xml COLUMNS val varchar2(20) PATH '.', col varchar2(20) PATH 'name(.)') x;
Max
http://oracleitalia.wordpress.com -
Pitty you did not mention your db version.
putting together some functions from FunctX one can writeSQL> with t as ( select xmltype('<?xml version="1.0"?> <purchase_order> <po_items> <item> <name>#Name#</name> <quantity>#number#</quantity> </item> </po_items> </purchase_order>') xml from dual ) select x.* from t t, xmltable('declare function local:index-of-node ( $nodes as node()* , $nodeToFind as node() ) as xs:integer* { for $seq in (1 to count($nodes)) return $seq[$nodes[$seq] is $nodeToFind] } ; declare function local:path-to-node-with-pos ( $node as node()? ) as xs:string { fn:string-join( for $ancestor in $node/ancestor-or-self::* let $sibsOfSameName := $ancestor/../*[fn:name() = fn:name($ancestor)] return fn:concat(fn:name($ancestor), if (fn:count($sibsOfSameName) <= 1) then "" else fn:concat( "[", local:index-of-node($sibsOfSameName,$ancestor),"]")) , "/") }; element e {local:path-to-node-with-pos(//item/name[. = "#Name#"]), element name {//name}}, element e {local:path-to-node-with-pos(//item/quantity[. = "#number#"]), element name {//quantity}}' passing t.xml columns value varchar2(50) path 'name', path varchar2(50) path 'text()' ) x VALUE PATH ---------- ---------------------------------------- #Name# purchase_order/po_items/item/name #number# purchase_order/po_items/item/quantity 2 rows selected.
-
But there you give a static value #number# and #Name#, but in my case. I need select all names what is in xml and get path of them!
Thanks anyway...it's helpful anyway -
there you give a static value #number# and #Name#can be avoided of course:
SQL> with t as ( select xmltype('<?xml version="1.0"?> <purchase_order> <po_items> <quantity>#number#</quantity> <item> <price>#Price#</price> <name>#Name#</name> <quantity>#number#</quantity> </item> </po_items> </purchase_order>') xml from dual ) -- -- select x.* from t t, xmltable('declare function local:index-of-node ( $nodes as node()* , $nodeToFind as node() ) as xs:integer* { for $seq in (1 to count($nodes)) return $seq[$nodes[$seq] is $nodeToFind] } ; declare function local:path-to-node-with-pos ( $node as node()? ) as xs:string { fn:string-join( for $ancestor in $node/ancestor-or-self::* let $sibsOfSameName := $ancestor/../*[fn:name() = fn:name($ancestor)] return fn:concat(fn:name($ancestor), if (fn:count($sibsOfSameName) <= 1) then "" else fn:concat( "[", local:index-of-node($sibsOfSameName,$ancestor),"]")) , "/") }; for $i in //po_items//* where ora:matches ($i, "^#\w+#$") return element e {local:path-to-node-with-pos($i), $i}' passing t.xml columns value varchar2(50) path '*', path varchar2(50) path 'text()' ) x / VALUE PATH ---------- ---------------------------------------- #number# purchase_order/po_items/quantity #Price# purchase_order/po_items/item/price #Name# purchase_order/po_items/item/name #number# purchase_order/po_items/item/quantity 4 rows selected.
-
Thanks a lot. Now I try to get understand the code I use it. I hope it's works fine.
This will works fine:
<p style-name="P1">
- <frame style-name="fr1" name="graphics1" anchor-type="paragraph" width="3.175cm" height="1.879cm" z-index="0">
<image xlink:href="Pictures/100002000000007800000047C659D6E8.gif" xlink:type="simple" xlink:show="embed" xlink:actuate="onLoad" />
</frame>
#DOME#
</p>
it's will select the dome and path of it?? -
this is simpler... ;-)
WITH tab AS (SELECT xmltype('<?xml version="1.0"?> <purchase_order> <po_items> <quantity>#number#</quantity> <item> <price>#Price#</price> <name>#Name#</name> <quantity>#number#</quantity> </item> </po_items> </purchase_order>') col FROM dual ) SELECT column_value FROM tab t, xmltable(' for $i in $tmp/descendant::* where $i/text() != "" return concat(string-join($i/ancestor-or-self::*/name(), "/"),"/","#",data($i),"#") ' passing t.col AS "tmp" )
Ravi Kumar
This discussion has been closed.