asked on

Get the parent node - XMLTYPE

Hello,

How can I get the parent node for all the elements in a XMLTYPE using ORACLE SQL? Here is the query I am using but I am not getting any data for parent.

example
for element ENOTIF, the parent node is 'test_tm_document'
for element BIR, the parent node is 'test_tm_document/ENOTIF'

drop table mytable1;
create table mytable1(xmldoc XMLtype);

insert into mytable1 values(
'<test-tm-document system-creator="MIBG" description="Application" document-subtype="APP" document-type="IBInput" version="1.0">
  <ENOTIF PUBDATE="20132580" RESTRICTCOUNT="0" NOTLANG="1">
    <BIR OOCD="TEST" TRANTYP="TNT" REGEDAT="20001105" EXPDATE="20241001">
      <COR CLID="515846">
        <NAME>
          <NAMEL>TESTTESTTESTTEST</NAMEL>
          <NAMEL>TESTTESTTESTTEST</NAMEL>
        </NAME>
        <ADDRESS>
          <ADDRL>TESTTESTTEST</ADDRL>
          <ADDRL>TESTTESTTESTTEST</ADDRL>
          <COUNTRY>TEST</COUNTRY>
       </ADDRESS>
      </COR>
      <HOLGR CLID="515845" NOTLANG="1">
        <NAME>
          <NAMEL>TESTTESTTESTTESTTEST</NAMEL>
        </NAME>
        <ADDRESS>
          <ADDRL>dfgdfgdfgt</ADDRL>
          <ADDRL>dfgdfgdfgdg</ADDRL>
          <COUNTRY>TEST</COUNTRY>
        </ADDRESS>
        <ENTNATL>TEST</ENTNATL>
        <LEGNATU>
          <LEGNATT>Registeres test</LEGNATT>
          <PLAINCO>Registeres test</PLAINCO>
        </LEGNATU>
      </HOLGR>
      <IMAGE TYPE="JPG" NAME="811357" COLOUR="N" TEXT="ResPAK"/>
      <MARDUR>10</MARDUR>
      <VIEN VIENVER="5">
        <VIECLAI>2705</VIECLAI>
      </VIEN>
      <BASICGS NICEVER="8">
        <GS NICCLAI="09">
          <GSTERMEN>test.</GSTERMEN>
        </GS>
      </BASICGS>
      <BASGR>
        <BASREGGR>
          <BASREGD>20001908</BASREGD>
          <BASREGN>236588</BASREGN>
        </BASREGGR>
       <BASAPPGR>
          <BASAPPD>20001908</BASAPPD>
          <BASAPPN>784065</BASAPPN>
        </BASAPPGR>
      </BASGR>
      <DESPG>
        <DCPCD>DF</DCPCD>
      </DESPG>
      <INTENTG>
        <CPCD>DF</CPCD>
      </INTENTG>
    </BIR>
  </ENOTIF>

</test-tm-document>');

SELECT T.*
FROM mytable1 mt,
     XMLTABLE('//*'
      passing mt.XMLDOC 
      COLUMNS
        element   VARCHAR(20) PATH 'name(.)',
        parent    VARCHAR(20) PATH '../name(.)') T

Open in new window

. Please help

Sean Stuber

something like this?

SELECT x.*
FROM mytable1 mt,
XMLTABLE(
'/node'
PASSING XMLQUERY('for $i in //*
where $i != ""
return <node childname="{$i/name(.)}" parentname="{$i/name(..)}"/>' PASSING BY VALUE mt.xmldoc RETURNING CONTENT)
COLUMNS child_element VARCHAR2(20) PATH '/node/@childname',
parent_element VARCHAR2(20) PATH '/node/@parentname') x

Sean Stuber

or maybe something like this?

SELECT x.*
  FROM mytable1 mt,
       XMLTABLE(
           '/node'
           PASSING XMLQUERY('declare function local:path-to-node( $nodes as node()* )  as xs:string* {$nodes/string-join(ancestor-or-self::*/name(.), "/") } ;
                             for $i in //*
                             where $i != ""
                             return <node childname="{$i/name(.)}" parentname="{$i/local:path-to-node(.)}"/>'
                      PASSING BY VALUE mt.xmldoc RETURNING CONTENT)
           COLUMNS child_element VARCHAR2(20) PATH '/node/@childname',
                   parent_element VARCHAR2(4000) PATH '/node/@parentname') x

Open in new window

angel7170

ASKER

This works great as expected. The only thing is it is missing the attributes. How to include that as well?

Sean Stuber

what are you expecting the output to look like?

angel7170

ASKER

For example under ENOTIF, there is PUBDATE, RESTRICTCOUNT, NOTLANG as attribute. that is missing from the select.

Sean Stuber

what are you expecting the output to look like?

show me some rows and columns of what you expect the output to be

angel7170

ASKER

Child_element parent_element
test-tm-document test-tm-document
ENOTIF test-tm-document/ENOTIF
PUBDATE test-tm-document /ENOTIF/@PUBDATE
RESTRICTCOUNT test-tm-document /ENOTIF/@RESTRICTCOUNT
BIR test-tm-document /ENOTIF/BIR

sorry, does this help?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

angel7170

ASKER

Perfect solution. Thank you very much