get path to nodes by value

754085

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

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

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

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

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

Massimo Ruocchio do you have some ideas??

730428

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

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

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

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

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

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