Why does the XMLAGG give me a different order than my query even though they have the same ORDER BY?
I can't figure out how to get my xml in the same order as my query. I tried using CONVERT(file_name, 'US7ASCII') along with LOWER to try to ignore accented characters and uppercase letters which made it better but it is still different.
I also tried to use COLLATE but maybe I just don't understand how it works or how to use it cause it did absolutely nothing.
So what am I missing? How can I get XMLAGG to give me the same result as the query?
Ideally I'd prefer not to have to modify this query unless there isn't any way to generate the XML with the same order.
SELECT id, file_name
FROM test_files
ORDER BY file_name;
Result:
| ID | File name |
|---|---|
| 1 | docx-01.docx |
| 2 | Docx-02.docx |
| 3 | docx-03.docx |
| 4 | doc-01.doc |
| 5 | Doc-02.doc |
| 6 | doc-03.doc |
| 7 | E .ppt |
| 8 | e .ppt |
| 9 | É .ppt |
| 10 | é .ppt |
| 11 | !é.ppt |
| 12 | _é.ppt |
| 13 | E.ppt |
| 14 | e.ppt |
| 15 | É.ppt |
| 16 | é.ppt |
| 17 | e0.ppt |
| 18 | é0.ppt |
| 19 | pdf-01.pdf |
| 20 | pdf-02.pdf |
| 21 | pdf-03.pdf |
| 22 | !.ppt |
| 23 | _.ppt |
| 24 | ppt-e .ppt |
| 25 | ppt-é .ppt |
| 26 | ppt-e.ppt |
| 27 | ppt-é.ppt |
| 28 | ppt-e0.ppt |
| 29 | ppt-é0.ppt |
| 30 | ppt-01.ppt |
| 31 | !0.ppt |
| 32 | _0.ppt |
NOTE: This is with the CONVERT and LOWER which is the closest I got to the result I want.
SELECT XMLELEMENT("files",
XMLAGG(XMLELEMENT("file",
XMLFOREST(id AS "id",
file_name AS "file_name"))
ORDER BY LOWER(CONVERT(file_name, 'US7ASCII'))))
FROM test_files;
Result:
<files>
<file><id>22</id><file_name>!.ppt</file_name></file>
<file><id>31</id><file_name>!0.ppt</file_name></file>
<file><id>11</id><file_name>!é.ppt</file_name></file>
<file><id>23</id><file_name>_.ppt</file_name></file>
<file><id>32</id><file_name>_0.ppt</file_name></file>
<file><id>12</id><file_name>_é.ppt</file_name></file>
<file><id>4</id> <file_name>doc-01.doc</file_name></file>
<file><id>5</id> <file_name>Doc-02.doc</file_name></file>
<file><id>6</id> <file_name>doc-03.doc</file_name></file>
<file><id>1</id> <file_name>docx-01.docx</file_name></file>
<file><id>2</id> <file_name>Docx-02.docx</file_name></file>
<file><id>3</id> <file_name>docx-03.docx</file_name></file>
<file><id>7</id> <file_name>E .ppt</file_name></file>
<file><id>8</id> <file_name>e .ppt</file_name></file>
<file><id>9</id> <file_name>É .ppt</file_name></file>
<file><id>10</id><file_name>é .ppt</file_name></file>
<file><id>13</id><file_name>E.ppt</file_name></file>
<file><id>14</id><file_name>e.ppt</file_name></file>
<file><id>15</id><file_name>É.ppt</file_name></file>
<file><id>16</id><file_name>é.ppt</file_name></file>
<file><id>17</id><file_name>e0.ppt</file_name></file>
<file><id>18</id><file_name>é0.ppt</file_name></file>
<file><id>19</id><file_name>pdf-01.pdf</file_name></file>
<file><id>20</id><file_name>pdf-02.pdf</file_name></file>
<file><id>21</id><file_name>pdf-03.pdf</file_name></file>
<file><id>30</id><file_name>ppt-01.ppt</file_name></file>
<file><id>24</id><file_name>ppt-e .ppt</file_name></file>
<file><id>25</id><file_name>ppt-é .ppt</file_name></file>
<file><id>26</id><file_name>ppt-e.ppt</file_name></file>
<file><id>27</id><file_name>ppt-é.ppt</file_name></file>
<file><id>28</id><file_name>ppt-e0.ppt</file_name></file>
<file><id>29</id><file_name>ppt-é0.ppt</file_name></file>
</files>
I manage to get my basic query to give me the same order as the XML query by adding a COLLATE binary_ai in the order by clause. However, I would still prefer not to make any modification to my basic query.
I would also appreciate to have some explanation on why an ORDER BY inside XMLAGG doesn't behave the same as on a query.
Based on the order the results of your original query appear in, you appear to have your NLS_SORT and NLS_COMP set to the default BINARY, and your NLS_LANGUAGE set to French (thanks for mentioning your language in your profile!).
It appears that the order by clause in XMLAGG doesn't honour the NLS settings.
Rather than trying to manipulate the values manually for ordering, you can use the NLSSORT function:
XMLAGG(... ORDER BY NLSSORT(file_name, 'NLS_SORT=XFRENCH'))
Using that, and with XMLSERIALIZE added just to format it for readability, you get:
SELECT XMLSERIALIZE(DOCUMENT XMLELEMENT("files",
XMLAGG(XMLELEMENT("file",
XMLFOREST(id AS "id",
file_name AS "file_name"))
ORDER BY NLSSORT(file_name, 'NLS_SORT=XFRENCH'))) INDENT SIZE = 2)
FROM test_files;
| XML |
|---|
| <files> <file> <id>1</id> <file_name>docx-01.docx</file_name> </file> <file> <id>2</id> <file_name>Docx-02.docx</file_name> </file> <file> <id>3</id> <file_name>docx-03.docx</file_name> </file> <file> <id>4</id> <file_name>doc-01.doc</file_name> </file> <file> <id>5</id> <file_name>Doc-02.doc</file_name> </file> <file> <id>6</id> <file_name>doc-03.doc</file_name> </file> <file> <id>7</id> <file_name>E .ppt</file_name> </file> <file> <id>8</id> <file_name>e .ppt</file_name> </file> <file> <id>9</id> <file_name>É .ppt</file_name> </file> <file> <id>10</id> <file_name>é .ppt</file_name> </file> <file> <id>11</id> <file_name>!é.ppt</file_name> </file> <file> <id>12</id> <file_name>_é.ppt</file_name> </file> <file> <id>13</id> <file_name>E.ppt</file_name> </file> <file> <id>14</id> <file_name>e.ppt</file_name> </file> <file> <id>15</id> <file_name>É.ppt</file_name> </file> <file> <id>16</id> <file_name>é.ppt</file_name> </file> <file> <id>17</id> <file_name>e0.ppt</file_name> </file> <file> <id>18</id> <file_name>é0.ppt</file_name> </file> <file> <id>19</id> <file_name>pdf-01.pdf</file_name> </file> <file> <id>20</id> <file_name>pdf-02.pdf</file_name> </file> <file> <id>21</id> <file_name>pdf-03.pdf</file_name> </file> <file> <id>22</id> <file_name>!.ppt</file_name> </file> <file> <id>23</id> <file_name>_.ppt</file_name> </file> <file> <id>24</id> <file_name>ppt-e .ppt</file_name> </file> <file> <id>25</id> <file_name>ppt-é .ppt</file_name> </file> <file> <id>26</id> <file_name>ppt-e.ppt</file_name> </file> <file> <id>27</id> <file_name>ppt-é.ppt</file_name> </file> <file> <id>28</id> <file_name>ppt-e0.ppt</file_name> </file> <file> <id>29</id> <file_name>ppt-é0.ppt</file_name> </file> <file> <id>30</id> <file_name>ppt-01.ppt</file_name> </file> <file> <id>31</id> <file_name>!0.ppt</file_name> </file> <file> <id>32</id> <file_name>_0.ppt</file_name> </file> </files> |
You can also order the query results before aggregating:
SELECT XMLSERIALIZE(DOCUMENT XMLELEMENT("files",
XMLAGG(XMLELEMENT("file",
XMLFOREST(id AS "id",
file_name AS "file_name"))
)) INDENT SIZE = 2)
FROM test_files
ORDER BY file_name;
... which Oracle recommends in support note 1407946.1; but I don't know if the order of the elements is actually guaranteed if you do that. Possibly it is, but I could imagine a future optimiser change, or maybe just a parallel query, affecting it.