get path to nodes by value

754085

And what it's return in your case??

ravikumar.sv

Gastons21 wrote:
And what it's return in your case??

not so sure what you mean by that. i think you asked me to show the result after executing...

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

754085

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

ravikumar.sv

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

754085

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 wright And 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

ravikumar.sv

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

754085

Ok..but there is output in xml, but how can I read datas into table of records??

ravikumar.sv

Gastons21 wrote:
Ok..but there is output in xml, but how can I read datas into table of records??

do you mean putting above selected data in a table??

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

754085

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;

ravikumar.sv

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