oracle xmltable get children nodes

Question

<SRDBSW xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="String"  ShortIds="Y" >
<OBJ_DF IdView="RW" ObjLevel="Element" Nature="" ObjectType="" ShortDescription="" ShortDescriptionCore="" LogicalShortDescription="" LongDescription="Reaction Wheel" LongDescriptionCore="" LogicalLongDescription="" Mnemonic="" IsDefined="Y" ModelType="" SerialNumber="" ProductTreeId="" CategoryFlag="7" OwnerFlag="7" InputVersion="" InputType="" InputReference="" >
   <TC_STR_DF IdView="TCSTABCDE" IsDirect="Y" CategoryFlag="0" OwnerFlag="0" ShortDescription="TC Packet Header Structure" ShortDescriptionCore="TC Packet Header Structure" LongDescription="Item: TC Structure" Mnemonic="TC Structure" Type="TC_STR" >
        <TC_STR_COMP_LIST>
            <TC_STR_COMP CompOrder="3" ComponentType="Editable parameter" CompId="HABCA" EngValue="3" TakeValue="From occurrence" MonParRef="MBCDA" Mandatory="Yes" />
        </TC_STR_COMP_LIST>
    </TC_STR_DF>
</OBJ_DF>

I have this section of XML in an xmltable in an Oracle table (this is just a sample, my XML is full with those sections):

    <SCOS_TM_DF IdView="1111" IsDirect="Y" CategoryFlag="0" OwnerFlag="0" ShortDescription="TC Packet Header Structure" ShortDescriptionCore="TC Packet Header Structure" LongDescription="Item: SCOS TM Packet" Mnemonic="SCOS TM Packet" Type="SCOS_TM" TpcfName="My TM packet" InterpretFlag="Both" DefOffsetTime="66" >
        <TM_STR_COMP_LIST>
            <TM_STR_COMP ComponentType="Single structure" CompId="TMSTABCDE" CompBytePos="0" CompBitPos="0" TimeOffset="0" SelectorParRef="MABCA" SelRawValue="0" />
        </TM_STR_COMP_LIST>
    </SCOS_TM_DF>

I need to find all ComponentType="Single structure", but also need to get the "father" SCOS_TM_DF IdView="1111".

So far, I'm working on this query, but can't get idview:

SELECT x.* 
FROM xmlimport t 
CROSS JOIN XMLTABLE ('/SRDBSW/OBJ_DF/TC_STR_DF/TC_STR_COMP_LIST/TC_STR_COMP/@*' 
          PASSING t.XMLDATA   
          COLUMNS 
            IdView VARCHAR2(30) PATH '/../../../@IdView', 
          CompId     VARCHAR2(30) PATH '@CompId', 
          attr_name      VARCHAR2(30) PATH 'local-name(.)', 
          attr_value     VARCHAR2(90) PATH '.' ) x  
          WHERE t.xmlkey = 'SRDB-XML-sample-1.xml';

Answer 1

You can also construct your own element in the XPath, which creates an attribute based on the parent node:

SELECT x.*
FROM xmlimport t
CROSS JOIN XMLTABLE ('for $i in //*[@ComponentType = ''Single structure'']
    return element {$i/name()} { attribute CompId {$i/@CompId},
      attribute IdView {$i/../../@IdView},
      $i }'
  PASSING t.XMLDATA
  COLUMNS IdView VARCHAR2(30) PATH '@IdView',
    CompId VARCHAR2(30) PATH '@CompId',
    attr_name VARCHAR2(30) PATH 'name(.)',
    attr_value VARCHAR2(90) PATH '.' 
) x
WHERE t.xmlkey = 'SRDB-XML-sample-1.xml';

I've made the node-name generic, so it matches all ComponentType="Single structure" as your question said you wanted; which means it matches the sample XML fragment.

I've sort of guessed what you want the attr_value to be, since your sample node didn't have any content.

With an expanded fragment:

<SRDBSW>
  <OBJ_DF>
    <SCOS_TM_DF IdView="1111" IsDirect="Y" CategoryFlag="0" OwnerFlag="0" ShortDescription="TC Packet Header Structure" ShortDescriptionCore="TC Packet Header Structure" LongDescription="Item: SCOS TM Packet" Mnemonic="SCOS TM Packet" Type="SCOS_TM" TpcfName="My TM packet" InterpretFlag="Both" DefOffsetTime="66" >
      <TM_STR_COMP_LIST>
        <TM_STR_COMP ComponentType="Single structure" CompId="TMSTABCDE" CompBytePos="0" CompBitPos="0" TimeOffset="0" SelectorParRef="MABCA" SelRawValue="0" />
      </TM_STR_COMP_LIST>
    </SCOS_TM_DF>
    <TC_STR_DF IdView="1112" IsDirect="Y" CategoryFlag="0" OwnerFlag="0" ShortDescription="TC Packet Header Structure" ShortDescriptionCore="TC Packet Header Structure" LongDescription="Item: SCOS TM Packet" Mnemonic="SCOS TM Packet" Type="SCOS_TM" TpcfName="My TM packet" InterpretFlag="Both" DefOffsetTime="66" >
      <TC_STR_COMP_LIST>
        <TC_STR_COMP ComponentType="Single structure" CompId="TCSTABCDE" CompBytePos="0" CompBitPos="0" TimeOffset="0" SelectorParRef="MABCA" SelRawValue="0">Test value</TC_STR_COMP>
      </TC_STR_COMP_LIST>
    </TC_STR_DF>
  </OBJ_DF>
</SRDBSW>

That gets:

IDVIEW   COMPID          ATTR_NAME            ATTR_VALUE    
-------- --------------- -------------------- ---------------
1111     TMSTABCDE       TM_STR_COMP                         
1112     TCSTABCDE       TC_STR_COMP          Test value     

SQL Fiddle demo.

Based on your comment that you now want the element name that the IdView comes from, you can just grab that as an additional attribute in the generated element:

SELECT x.*
FROM xmlimport t
CROSS JOIN XMLTABLE (q'#for $i in //*[@ComponentType = 'Single structure']
    return element {$i/name()} {attribute IdView {$i/../../@IdView},
      attribute TopName {$i/../../name()},
      $i }#'
  PASSING t.XMLDATA COLUMNS IdView VARCHAR2(30) PATH '@IdView',
    TopName VARCHAR2(30) PATH '@TopName',
    attr_name VARCHAR2(30) PATH 'name(.)',
    attr_value VARCHAR2(90) PATH '.' 
) x
WHERE t.xmlkey = 'SRDB-XML-sample-1.xml';

IDVIEW   TOPNAME         ATTR_NAME            ATTR_VALUE    
-------- --------------- -------------------- ---------------
1111     SCOS_TM_DF      TM_STR_COMP                         
1112     TC_STR_DF       TC_STR_COMP          Test value     

I've also switched to using the alternative quote syntax, which means you don't have to escape the single-quotes around the Single structure value. You just need a delimiter that definitely won't appear inside the actual string, the XPath in this case. I'd usually use square brackets by default, personally, but since those do appear in the XPath for the attribute match, I've used # instead.

SQL Fiddle.

Answer 2

As far as I understand what you're trying to do, IdView should contain the @IdView attribute of the <SCOS_TM_DF> ancestor.

You're using an absolute path starting at the root node (/../../../@IdView), from where you're ascending even further (to elements above the root node that cannot exist). What you probably want to do is ascending from the current context ., eg.

IdView VARCHAR2(30) PATH './../../../@IdView'

Furthermore, the XPath expression you're using in the third line does not match your XML input (where's an SCOS_TM_DF axis step?). As you did not provide the full document structure, it's hard to help you with further details here.