Forum Stats

  • 3,824,982 Users
  • 2,260,448 Discussions
  • 7,896,369 Comments

Discussions

get path to nodes by value

754085
754085 Member Posts: 45
edited Feb 19, 2010 10:11AM in SQL & PL/SQL
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

Best Answer

  • 730428
    730428 Member Posts: 2,087
    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
«13

Answers

  • 730428
    730428 Member Posts: 2,087
    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
    730428 Member Posts: 2,087
    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
  • 754085
    754085 Member Posts: 45
    edited Feb 18, 2010 4:43AM
    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
    754085 Member Posts: 45
    Massimo Ruocchio do you have some ideas??
  • 730428
    730428 Member Posts: 2,087
    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
    MichaelS Member Posts: 8,424 Bronze Crown
    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
    754085 Member Posts: 45
    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
    MichaelS Member Posts: 8,424 Bronze Crown
    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.
    MichaelS
  • 754085
    754085 Member Posts: 45
    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
    ravikumar.sv Member Posts: 1,072
    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.