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';

Please have a look at the [FAQ] regarding formatting using Markdown. — Jens Erat, May 29 '15 at 11:36

Alex Poole · Answer 1 · 2015-06-01T15:59:29.113

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.

@AntónioRoque - I don't know what that means - is it good or bad? You haven't said in the question what output you expect (or given complete XML that matches the condition you said you want). I've added a SQL Fiddle link to the answer, showing the data I'm using and its output. [You can also run this code with your original fragment](http://sqlfiddle.com/#!4/4957a1/1). — Alex Poole, May 29 '15 at 11:55
I would just like to "extract" IDview value of SCOS_TM_DF, the "SCOS_TM_DF" tag/text , and the "TC_STR_COMP" tag/text of the element that is [@ComponentType = ''Single structure''] — António Roque, May 29 '15 at 12:27
i have made same change in your query SELECT x.* FROM xmlimport t CROSS JOIN XMLTABLE ('for $i in //*[@ComponentType = ''Single structure''] return element {$i/local-name()} {attribute IdView {$i/../../@IdView}, $i }' PASSING t.XMLDATA COLUMNS IdView VARCHAR2(30) PATH '@IdView', attr_name VARCHAR2(30) PATH 'local-name(.)', attr_value VARCHAR2(90) PATH '.' ) x WHERE t.xmlkey = 'SRDB-XML-sample-1.xml'; — António Roque, May 29 '15 at 13:28
just need to find a way to get the name of top element "SCOS_TM_DF" — António Roque, May 29 '15 at 13:29
@AntónioRoque - as far as I can see you just dropped the CompId attribute? I've added a version that get's the name of the element the IdView comes from. — Alex Poole, May 29 '15 at 13:40
thank , THANK You .. I´m new on xmltable xquery and xpath .. I have sooooo much to learn ... just just one question the diferent local-name and name ??? , I would never poll this off without your precious help , I mean All you guys helpe me .. thank you — António Roque, May 29 '15 at 13:55
@AntónioRoque - they're the same [unless you have namespaces](http://stackoverflow.com/a/2464906/266304), which you don't. I've changed the code to use `name()` consistently, but using `local-name()` consistently would also be OK here. — Alex Poole, May 29 '15 at 14:03
thank you , (q'#for $i in //*[@ComponentType = 'Single structure'] what is q'# .? ???? — António Roque, Jun 01 '15 at 15:50
@AntónioRoque - alternative quote syntax, with # as a delimiter. I've added a link to the docs that explain that; I thought I'd included that when I edited the answer last time. Added a second Fiddle too. — Alex Poole, Jun 01 '15 at 16:00
sorry didn´t notice at the first time ... the relation with q'# and the link with the explanation.. I am feeling very rookie ... with my last question .. I´m sorry , Thank you. — António Roque, Jun 01 '15 at 16:31

score 0 · Answer 2 · answered May 29 '15 at 11:14

0

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.

answered May 29 '15 at 11:14

Jens Erat

37,523
16
80
96

he ./ isn´t working the struture of this section is SRDBSW->OBJ_DF->TC_STR_DF->TC_STR_COMP_LIST->TM_STR_COMP .. do not understand what you mean by (where's an SCOS_TM_DF axis step?). – António Roque May 29 '15 at 11:27
I going the add the xml at the top – António Roque May 29 '15 at 11:31
There's an `` element, but no axis step step in your query for traversing this element. The XPath expression does not match your XML snippet. I don't get what you want to express with the first part of your comment. – Jens Erat May 29 '15 at 11:37
yes I need IdView attribute of the . – António Roque May 29 '15 at 11:37
ok my query is a huge "mess" .. from the xml that I added to the initial question , is possible to "extract" IDview value of SCOS_TM_DF, the "SCOS_TM_DF" tag/text , and the "TC_STR_COMP" tag/text of the element that is [@ComponentType = ''Single structure''] ... ? – António Roque May 29 '15 at 13:23
is possible to reuse this ??? ... SELECT x.* FROM xmlimport t CROSS JOIN XMLTABLE ('for $i in //*[@ComponentType = ''Single structure''] return element {$i/local-name()} {attribute IdView {$i/../../@IdView}, $i }' PASSING t.XMLDATA COLUMNS IdView VARCHAR2(30) PATH '@IdView', attr_name VARCHAR2(30) PATH 'local-name(.)', attr_value VARCHAR2(90) PATH '.' ) x WHERE t.xmlkey = 'SRDB-XML-sample-1.xml'; – António Roque May 29 '15 at 13:28

oracle xmltable get children nodes

2 Answers2

Linked