Bonjour,
Je m'aperçois que l'utilisation de la clause ORDER BY n'est pas possible dans les requètes XML. Je ne sais pas trop comment m'en passer. Voici un exemple :La requète me renvoie les lignes attendues :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61 CREATE TABLE AIRWAY_TMP ( AIRWAY_NAME VARCHAR2(20) NOT NULL ) ; REM REM Index : AIRWAY_TMP_I1 REM execute dropindex('AIRWAY_TMP') CREATE UNIQUE INDEX AIRWAY_TMP_I1 ON AIRWAY_TMP ( AIRWAY_NAME ASC ) ; INSERT INTO AIRWAY_TMP (select 'A1' from dual); INSERT INTO AIRWAY_TMP (select 'A2' from dual); execute droptable ('AIRWAY_SECT_TMP') CREATE TABLE AIRWAY_SECT_TMP ( AIRWAY_NAME VARCHAR2(20) NOT NULL, POINT_NAME VARCHAR2(5) NOT NULL ) ; REM REM Index : AIRWAY_SECT_TMP_I1 REM execute dropindex('AIRWAY_SECT_TMP') CREATE UNIQUE INDEX AIRWAY_SECT_TMP_I1 ON AIRWAY_SECT_TMP ( AIRWAY_NAME ASC, POINT_NAME ASC ) ; INSERT INTO AIRWAY_SECT_TMP (select 'A1','PT1_1' from dual); INSERT INTO AIRWAY_SECT_TMP (select 'A1','PT1_2' from dual); INSERT INTO AIRWAY_SECT_TMP (select 'A2','PT2_1' from dual); INSERT INTO AIRWAY_SECT_TMP (select 'A2','PT2_2' from dual); INSERT INTO AIRWAY_SECT_TMP (select 'A2','PT2_3' from dual); SELECT XMLPARSE(document XMLSERIALIZE(document( SELECT XMLELEMENT("GraphicalLineList", (SELECT XMLAgg( XMLELEMENT("GraphicalLine", XMLELEMENT("PointList",a.AIRWAY_NAME), XMLELEMENT("PointList", (SELECT XMLAgg( XMLELEMENT("GeographicalPoint",p.POINT_NAME)) FROM AIRWAY_SECT_TMP p WHERE p.AIRWAY_NAME=a.AIRWAY_NAME)))) FROM AIRWAY_TMP a) ) FROM dual ))) FROM dual;Mais si j'ajoute un ORDER BY dans le sous select, j'ai :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 XMLPARSE(DOCUMENTXMLSERIALIZE(DOCUMENT(SELECTXMLELEMENT("GRAPHICALLINELIST",(SEL -------------------------------------------------------------------------------- <GraphicalLineList> <GraphicalLine> <PointList>A1</PointList> <PointList> <GeographicalPoint>PT1_1</GeographicalPoint> <GeographicalPoint>PT1_2</GeographicalPoint> </PointList> </GraphicalLine> <GraphicalLine> <PointList>A2</PointList> <PointList> XMLPARSE(DOCUMENTXMLSERIALIZE(DOCUMENT(SELECTXMLELEMENT("GRAPHICALLINELIST",(SEL -------------------------------------------------------------------------------- <GeographicalPoint>PT2_1</GeographicalPoint> <GeographicalPoint>PT2_2</GeographicalPoint> <GeographicalPoint>PT2_3</GeographicalPoint> </PointList> </GraphicalLine> </GraphicalLineList>Je ne vois pas ce qui empêche de trier. Comment puis je ordonner mes résultats ?
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 SQL> SELECT XMLPARSE(document XMLSERIALIZE(document( SELECT 2 3 XMLELEMENT("GraphicalLineList", 4 (SELECT XMLAgg( 5 XMLELEMENT("GraphicalLine", 6 XMLELEMENT("PointList",a.AIRWAY_NAME), 7 XMLELEMENT("PointList", 8 (SELECT XMLAgg( 9 XMLELEMENT("GeographicalPoint",p.POINT_NAME)) 10 FROM AIRWAY_SECT_TMP p 11 WHERE p.AIRWAY_NAME=a.AIRWAY_NAME ORDER BY p.POINT_NAME)))) 12 FROM AIRWAY_TMP a) 13 ) FROM dual 14 ))) FROM dual; WHERE p.AIRWAY_NAME=a.AIRWAY_NAME ORDER BY p.POINT_NAME)))) * ERROR at line 11: ORA-00907: missing right parenthesis
Merci
Partager