How to use Order by,Group by , Count() etc. operation on table having XMLType column?

up vote
0
down vote

favorite

My table data look like this..

 <OneAirport>

     <Rank>3</Rank>

     <Airport>Dubai International</Airport>

     <Location>Garhoud</Location>

     <Country>United Arab Emirates</Country>

      <Code_IATA>DXB</Code_IATA>

      <Code_ICAO>OMDB</Code_ICAO>

      <Total_passenger>88242099</Total_passenger>

     <Rank_change>0</Rank_change>

     <Percent_Change>5.5</Percent_Change>

 </OneAirport>

I am trying to use SQL clauses like order by, group by on XmlElements(tags) but getting error...
Is there any way i can sort this data tag vise?

My Code:

 select extract(airport, '/')"airports2017XML" from AIRPORTS2017XML

 order by EXTRACTVALUE(airport,'/OneAirport/Country') ASC ;

My Output:

Excluding the 1st two rows, rest of the output is correct .I don't know why these 2 rows are not sorted ??

enter image description here

edited Nov 10 at 19:08

Ken White

106k11151310

asked Nov 7 at 5:06

I_am_Vits

I didn't get exactly what you need ... please post what you tried and whats the expectation
– kanagaraj
Nov 7 at 15:09

I tried using extract(),extractvalue() along with order by and group by but got error.I am looking to sort my xml data in table based on specific tags. Is it possible?
– I_am_Vits
Nov 7 at 17:24

I haven't tried this ...just refer and see if this helps you stackoverflow.com/questions/32824304/…
– kanagaraj
Nov 7 at 18:16

Got it. Thanks for the reference.
– I_am_Vits
Nov 7 at 18:21

add a comment |

up vote
0
down vote

favorite

My table data look like this..

 <OneAirport>

     <Rank>3</Rank>

     <Airport>Dubai International</Airport>

     <Location>Garhoud</Location>

     <Country>United Arab Emirates</Country>

      <Code_IATA>DXB</Code_IATA>

      <Code_ICAO>OMDB</Code_ICAO>

      <Total_passenger>88242099</Total_passenger>

     <Rank_change>0</Rank_change>

     <Percent_Change>5.5</Percent_Change>

 </OneAirport>

I am trying to use SQL clauses like order by, group by on XmlElements(tags) but getting error...
Is there any way i can sort this data tag vise?

My Code:

 select extract(airport, '/')"airports2017XML" from AIRPORTS2017XML

 order by EXTRACTVALUE(airport,'/OneAirport/Country') ASC ;

My Output:

Excluding the 1st two rows, rest of the output is correct .I don't know why these 2 rows are not sorted ??

enter image description here

edited Nov 10 at 19:08

Ken White

106k11151310

asked Nov 7 at 5:06

I_am_Vits

I didn't get exactly what you need ... please post what you tried and whats the expectation
– kanagaraj
Nov 7 at 15:09

I tried using extract(),extractvalue() along with order by and group by but got error.I am looking to sort my xml data in table based on specific tags. Is it possible?
– I_am_Vits
Nov 7 at 17:24

I haven't tried this ...just refer and see if this helps you stackoverflow.com/questions/32824304/…
– kanagaraj
Nov 7 at 18:16

Got it. Thanks for the reference.
– I_am_Vits
Nov 7 at 18:21

add a comment |

up vote
0
down vote

favorite

My table data look like this..

 <OneAirport>

     <Rank>3</Rank>

     <Airport>Dubai International</Airport>

     <Location>Garhoud</Location>

     <Country>United Arab Emirates</Country>

      <Code_IATA>DXB</Code_IATA>

      <Code_ICAO>OMDB</Code_ICAO>

      <Total_passenger>88242099</Total_passenger>

     <Rank_change>0</Rank_change>

     <Percent_Change>5.5</Percent_Change>

 </OneAirport>

I am trying to use SQL clauses like order by, group by on XmlElements(tags) but getting error...
Is there any way i can sort this data tag vise?

My Code:

 select extract(airport, '/')"airports2017XML" from AIRPORTS2017XML

 order by EXTRACTVALUE(airport,'/OneAirport/Country') ASC ;

My Output:

Excluding the 1st two rows, rest of the output is correct .I don't know why these 2 rows are not sorted ??

enter image description here

edited Nov 10 at 19:08

Ken White

106k11151310

asked Nov 7 at 5:06

I_am_Vits

My table data look like this..

 <OneAirport>

     <Rank>3</Rank>

     <Airport>Dubai International</Airport>

     <Location>Garhoud</Location>

     <Country>United Arab Emirates</Country>

      <Code_IATA>DXB</Code_IATA>

      <Code_ICAO>OMDB</Code_ICAO>

      <Total_passenger>88242099</Total_passenger>

     <Rank_change>0</Rank_change>

     <Percent_Change>5.5</Percent_Change>

 </OneAirport>

I am trying to use SQL clauses like order by, group by on XmlElements(tags) but getting error...
Is there any way i can sort this data tag vise?

My Code:

 select extract(airport, '/')"airports2017XML" from AIRPORTS2017XML

 order by EXTRACTVALUE(airport,'/OneAirport/Country') ASC ;

My Output:

Excluding the 1st two rows, rest of the output is correct .I don't know why these 2 rows are not sorted ??

enter image description here

xml oracle plsqldeveloper

edited Nov 10 at 19:08

Ken White

106k11151310

asked Nov 7 at 5:06

I_am_Vits

edited Nov 10 at 19:08

Ken White

106k11151310

asked Nov 7 at 5:06

I_am_Vits

edited Nov 10 at 19:08

Ken White

106k11151310

edited Nov 10 at 19:08

Ken White

106k11151310

edited Nov 10 at 19:08

Ken White

106k11151310

asked Nov 7 at 5:06

I_am_Vits

asked Nov 7 at 5:06

I_am_Vits

asked Nov 7 at 5:06

I_am_Vits

I didn't get exactly what you need ... please post what you tried and whats the expectation
– kanagaraj
Nov 7 at 15:09

I tried using extract(),extractvalue() along with order by and group by but got error.I am looking to sort my xml data in table based on specific tags. Is it possible?
– I_am_Vits
Nov 7 at 17:24

I haven't tried this ...just refer and see if this helps you stackoverflow.com/questions/32824304/…
– kanagaraj
Nov 7 at 18:16

Got it. Thanks for the reference.
– I_am_Vits
Nov 7 at 18:21

add a comment |

I didn't get exactly what you need ... please post what you tried and whats the expectation
– kanagaraj
Nov 7 at 15:09

I tried using extract(),extractvalue() along with order by and group by but got error.I am looking to sort my xml data in table based on specific tags. Is it possible?
– I_am_Vits
Nov 7 at 17:24

I haven't tried this ...just refer and see if this helps you stackoverflow.com/questions/32824304/…
– kanagaraj
Nov 7 at 18:16

Got it. Thanks for the reference.
– I_am_Vits
Nov 7 at 18:21

I didn't get exactly what you need ... please post what you tried and whats the expectation
– kanagaraj
Nov 7 at 15:09

I tried using extract(),extractvalue() along with order by and group by but got error.I am looking to sort my xml data in table based on specific tags. Is it possible?
– I_am_Vits
Nov 7 at 17:24

I haven't tried this ...just refer and see if this helps you stackoverflow.com/questions/32824304/…
– kanagaraj
Nov 7 at 18:16

Got it. Thanks for the reference.
– I_am_Vits
Nov 7 at 18:21

add a comment |

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53183847%2fhow-to-use-order-by-group-by-count-etc-operation-on-table-having-xmltype-co%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

active

oldest

votes

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ndtyjky