parent value of xmlquery [message #675981] |
Fri, 03 May 2019 06:28 |
|
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 |
_jum
Messages: 577 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 #675991 is a reply to message #675986] |
Fri, 03 May 2019 10:07 |
_jum
Messages: 577 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
|
|
|