sql - How to extract element-path from XMLType Node? -

i have select statement on xml document , 1 column should return me path of each node.

for example, given data

select *  table(xmlsequence(   xmltype('<?xml version="1.0"?>     <users><user><name>user1</name></user>            <user><name>user2</name></user>            <group>               <user><name>user3</name></user>            </group>            <user><name>user4</name></user>     </users>').extract('/*//*[text()]'))) t; 

which results in

column_value -------- <user><name>user1</name></user> <user><name>user2</name></user> <user><name>user3</name></user> <user><name>user4</name></user> 

i'd have result this:

path                     value ------------------------ -------------- /users/user/name         user1 /users/user/name         user2 /users/group/user/name   user3 /users/user/name         user4 

i can not see how this. figure there 2 thing have work properly:

• can extract path xmltype single operation or method, or have string-magic?
• what correct xpath expression whole element path (if thats possible), eg. <users><group><user><name>user3</name></user></group></user> insead of <user><name>user3</name></user>?

maybe not understanding xmltype fully, yet. need different approach, can not see it.

sidenotes:

• in final version xml document coming clobs of table, not static document.
• the path column can of course use dots or whatever , initial slash not issue, representation do.
• also not mind if every inner node gets result row (possibly null value), not ones text() in (which interested in).
• in end need tail element of path separate (always "name" in example here, vary later), i.e. ('/users/groups/user', 'name', 'user3'), can deal separately.

you can achieve of xmltable function oracle xml db xquery function set:

select *    xmltable(     '      declare function local:path-to-node( $nodes node()* )  xs:string* {        $nodes/string-join(ancestor-or-self::*/name(.), ''/'')      };      $i in $rdoc//name         return <ret><name_path>{local:path-to-node($i)}</name_path>{$i}</ret>     '     passing      xmlparse(content '       <users><user><name>user1</name></user>            <user><name>user2</name></user>            <group>               <user><name>user3</name></user>            </group>            <user><name>user4</name></user>       </users>'     )     "rdoc"     columns        name_path  varchar2(4000) path '//ret/name_path',       name_value varchar2(4000) path '//ret/name'    ) 

for me xquery looks @ least more intuitive xml data manipulation xslt.

you can find useful set of xquery functions here.

update 1

i suppose need totally plain dataset full data @ last stage. target can reached complicated way, constructed step-by-step below, variant resource-angry. propose review final target (selecting specific records, count number of elements etc.) , after simplify solution or totally change it.

update 2

all steps deleted update except last because @a.b.cade proposed more elegant solution in comments. solution provided in update 3 section below.

step 1 - constructing dataset of id's corresponding query results

step 2 - aggregating single xml row

step 3 - full plain dataset querying constracted xml xmltable

with xmlsource (   -- purpose write long string once   select '       <users><user><name>user1</name></user>            <user><name>user2</name></user>            <group>               <user><name>user3</name></user>            </group>            <user><name>user4</name></user>       </users>' xml_string    dual    ), xml_table (    -- model of xmltable   select 10 id, xml_string xml_data xmlsource union    select 20 id, xml_string xml_data xmlsource union    select 30 id, xml_string xml_data xmlsource  )  select  *   xmltable(     '         $entry_user in $full_doc/full_list/list_entry/name_info           return <tuple>                    <id>{data($entry_user/../@id_value)}</id>                    <path>{$entry_user/name_path/text()}</path>                    <name>{$entry_user/name_value/text()}</name>                   </tuple>      '     passing (        select           xmlelement("full_list",            xmlagg(                  xmlelement("list_entry",               xmlattributes(id "id_value"),               xmlquery(                 '                  declare function local:path-to-node( $nodes node()* )  xs:string* {                    $nodes/string-join(ancestor-or-self::*/name(.), ''/'')                  };(: function construct path :)                   $i in $rdoc//name return <name_info><name_path>{local:path-to-node($i)}</name_path><name_value>{$i/text()}</name_value></name_info>                 '                 passing value xmlparse(content xml_data) "rdoc"                 returning content               )             )           )         )                 xml_table     )        "full_doc"           columns       id_val   varchar2(4000) path '//tuple/id',       path_val varchar2(4000) path '//tuple/path',       name_val varchar2(4000) path '//tuple/name'   )     

update 3

as mentioned @a.b.cade in comment, there simple way join id's xquery results.

because don't external links in answers, code below represents his sql fiddle, little bit adapted data source answer:

with xmlsource (   -- purpose write long string once   select '       <users><user><name>user1</name></user>            <user><name>user2</name></user>            <group>               <user><name>user3</name></user>            </group>            <user><name>user4</name></user>       </users>' xml_string    dual    ), xml_table (    -- model of xmltable   select 10 id, xml_string xml_data xmlsource union    select 20 id, xml_string xml_data xmlsource union   select 30 id, xml_string xml_data xmlsource ) select xd.id, x.*  xml_table xd,   xmltable(     'declare function local:path-to-node( $nodes node()* )  xs:string* {$nodes/string-join(ancestor-or-self::*/name(.), ''/'')     };     $i in $rdoc//name        return <ret><name_path>{local:path-to-node($i)}</name_path>{$i}</ret>    '     passing     xmlparse(content xd.xml_data     )     "rdoc"     columns       name_path  varchar2(4000) path '//ret/name_path',       name_value varchar2(4000) path '//ret/name'    ) x