get path to nodes by value

754085 · Feb 18, 2010 3:34AM

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

730428 · Feb 18, 2010 3:42AM

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

730428 · Feb 18, 2010 3:39AM

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

730428 · Feb 18, 2010 3:42AM

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

754085 · Feb 18, 2010 3:44AM

I'm not think this to get in result:

NAME                 QTY
----------------    -----------------
#Name#             #number#

in my result needs

VALUE                PATH
--------------      ------------------------------
#Name#            /purchase_order/po_items/item/name
#number#        /purchase_order/po_items/item/quantity

754085 · Feb 18, 2010 4:14AM

Massimo Ruocchio do you have some ideas??

730428 · Feb 18, 2010 8:59AM

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

MichaelS · Feb 18, 2010 9:14AM

Pitty you did not mention your db version.

putting together some functions from FunctX one can write

SQL> 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.

754085 · Feb 19, 2010 2:02AM

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

MichaelS · Feb 19, 2010 2:45AM

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.

754085 · Feb 19, 2010 3:34AM

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??

ravikumar.sv · Feb 19, 2010 4:16AM

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

Forum Stats

Quick Links

Categories

get path to nodes by value

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

get path to nodes by value

Best Answer

Answers