Home » SQL & PL/SQL » SQL & PL/SQL » parent value of xmlquery (Oracle 12C)
parent value of xmlquery [message #675981] Fri, 03 May 2019 06:28 Go to next message
hakan_8
Messages: 3
Registered: May 2019
Junior Member
<Cases>
 <Case tag="IT653294" requesting="IT" >
       <Races>
        <Race scheduled="2018-06-01" id="100"/>
       </Races>
    </Case>
   <Case tag="IT831787" requesting="AB">
      <Races>
       <Race scheduled="2018-06-02" id="101" />
       <Race scheduled="2018-06-02" id="102" />
      </Races>
   </Case>
<Cases>

SELECT  
    FROM xml  xx
    ,XMLTABLE (XMLNAMESPACES (DEFAULT 'some_url') ,
    'for $i in Cases/Case/Races/Race return $i'
               PASSING xx.XMLDOC 
               COLUMNS scheduled  Date PATH '@scheduled'
                          id      NUMBER PATH '@id' ) x


this gives :

2018-06-01 100
2018-06-02 101
2018-06-02 102

I need the value of a parent node attribute

What I actually need is :
2018-06-01 100 IT653294
2018-06-02 101 IT831787
2018-06-02 102 IT831787

How can I get the parent value? Every help would be very appreciated.

Thanks in advance

Re: parent value of xmlquery [message #675982 is a reply to message #675981] Fri, 03 May 2019 07:24 Go to previous messageGo to next message
_jum
Messages: 557
Registered: February 2008
Senior Member
You don't need XQuery FLWOR, try:
WITH xdata(xd) AS
 (SELECT XMLTYpe(
 '<Cases>
   <Case tag="IT653294" requesting="IT" >
       <Races>
        <Race scheduled="2018-06-01" id="100"/>
       </Races>
    </Case>
   <Case tag="IT831787" requesting="AB">
      <Races>
       <Race scheduled="2018-06-02" id="101" />
       <Race scheduled="2018-06-03" id="102" />
      </Races>
   </Case>
</Cases>') FROM dual)
SELECT scheduled, cid, tag  
  FROM xdata  
     , XMLTABLE ('/Cases/Case'
         PASSING xdata.xd 
         COLUMNS tag VARCHAR2(200) PATH '@tag'
               , rx  XMLTYPE PATH '/Case/Races') x
     , XMLTABLE ('/Races/Race'
         PASSING rx 
         COLUMNS cid VARCHAR2(200) PATH '@id'
              , scheduled VARCHAR2(200) PATH '@scheduled') y 

SCHEDULED CID TAG
-------------------------------------
2018-06-01 100 IT653294
2018-06-02 101 IT831787
2018-06-03 102 IT831787

[Updated on: Fri, 03 May 2019 07:26]

Report message to a moderator

Re: parent value of xmlquery [message #675986 is a reply to message #675982] Fri, 03 May 2019 08:41 Go to previous messageGo to next message
hakan_8
Messages: 3
Registered: May 2019
Junior Member
Hello _Jum,
Thanks for you quick reply. However I need the XQuery version of it because the whole XML is read from the database with XQuery. This is just a part of the select statement. Isn't it possible to expand my select statement to retrieve the tag from the parent? I saw functions like parent:: and root:: but haven't succeeded in using them. The whole code is based on XQuery so helping me to solve it with the used tools would be very appreciated.

Thanks for you reply
Re: parent value of xmlquery [message #675990 is a reply to message #675986] Fri, 03 May 2019 10:02 Go to previous messageGo to next message
hakan_8
Messages: 3
Registered: May 2019
Junior Member
Is my assumption correct that I Can't retrieve the value of tag from the parent node? Ofcourse this construction gives 6 records instead of 3 and with the wrong values, because this gives every scheduled races the both tags instead of only the child.


SELECT  x.scheduled, y.tag, x.id
FROM xml  xx
,XMLTABLE (XMLNAMESPACES (DEFAULT 'some_url') , 'Data/OrganizedRaces/Cases/Case/Races/Race'
           PASSING xx.XMLDOC
           COLUMNS scheduled           varchar2(20)     PATH '@scheduled'
            ,      id                  number           PATH '@actual') x
,XMLTABLE (XMLNAMESPACES (DEFAULT 'some_url') , 'Data/OrganizedRaces/Cases/Case'
               PASSING xx.XMLDOC
               COLUMNS tag                varchar2(200) PATH '@tag') y  <-- this should be retrieved from the parent 

I would really appreciate it if I get a solution which is close to my solution by using XQuery

thanks you all
Re: parent value of xmlquery [message #675991 is a reply to message #675986] Fri, 03 May 2019 10:07 Go to previous message
_jum
Messages: 557
Registered: February 2008
Senior Member
Why do you need XQuery, don't get it?!
But of course you can obtain parents by "climbing up" using parent:: or ancestor:: or ..
Compare:
WITH xdata(xd) AS
 (SELECT XMLTYpe(
 '<Cases>
   <Case tag="IT653294" requesting="IT" >
       <Races>
        <Race scheduled="2018-06-01" id="100"/>
       </Races>
    </Case>
   <Case tag="IT831787" requesting="AB">
      <Races>
       <Race scheduled="2018-06-02" id="101" />
       <Race scheduled="2018-06-03" id="102" />
      </Races>
   </Case>
</Cases>') FROM dual)
SELECT scheduled, cid, tag1, tag2  
  FROM xdata  
--     , XMLTABLE ('/Cases/Case'
--         PASSING xdata.xd 
--         COLUMNS tag VARCHAR2(200) PATH '@tag'
--               , rx  XMLTYPE PATH '/Case/Races'
--               , ) x
     , XMLTABLE ('/Cases/Case/Races/Race'
         PASSING xdata.xd 
         COLUMNS cid VARCHAR2(200) PATH '@id'
               , scheduled VARCHAR2(200) PATH '@scheduled'
               , tag1 VARCHAR2(200) PATH './../../@tag'
               , tag2 VARCHAR2(200) PATH './ancestor::node()/parent::node()/@tag'
               ) y 



SCHEDULED CID TAG1 TAG2
----------------------------------------------------
2018-06-01 100 IT653294 IT653294
2018-06-02 101 IT831787 IT831787
2018-06-03 102 IT831787 IT831787

[Updated on: Fri, 03 May 2019 10:09]

Report message to a moderator

Previous Topic: ORA-00933: SQL command not properly ended
Next Topic: How to remove line breaks and put it in excel
Goto Forum:
  


Current Time: Sat Nov 16 18:06:25 CST 2019