Forum Stats

  • 3,826,577 Users
  • 2,260,666 Discussions
  • 7,897,007 Comments

Discussions

get path to nodes by value

2

Answers

  • 754085
    754085 Member Posts: 45
    And what it's return in your case??
  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    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
    754085 Member Posts: 45
    edited Feb 19, 2010 5:30AM
    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
    ravikumar.sv Member Posts: 1,072
    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
    754085 Member Posts: 45
    edited Feb 19, 2010 6:22AM
    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
    ravikumar.sv Member Posts: 1,072
    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
    ravikumar.sv
  • 754085
    754085 Member Posts: 45
    Ok..but there is output in xml, but how can I read datas into table of records??
  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    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
    ravikumar.sv
  • 754085
    754085 Member Posts: 45
    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
    ravikumar.sv Member Posts: 1,072
    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
    ravikumar.sv
This discussion has been closed.