Question How to sort data in a xml file target?

Hi,

I'm looking for a way to sort (order by) data when I load them from a MS SQL table to a xml file.
I didn't find any option in the Integration Template, how can I do it?

Thanks,

Hi,

To order the data when loading an XML file, the idea is to set the rule on the repetition key generating the XML node.
For this, concatenate all the source columns you would have put in the ORDER BY SQL query.

As an example, I am ordering here the data by CUS_TITLE and CUS_ID:


The value used for the repetition key is:
Note:

• The order in which you concatenate the column is important, like the order in an ORDER BY SQL statement.
• This solution works also on Flat Files, like explained in this article:
  stambia.org/doc/130-technology-articles/...-a-hierarchical-file

Does this help to perform what you wanted?

Hi,

Yes its works!
But, how can I sort a string value in DESC order?

Thanks

Hi,

For more advanced sorting, you can use a stage and the Analytic / Ranking functions of your the Database to generate a key corresponding to your sort for each of your lines.

Here is an example with Microsoft SQL Server:

1. Add a stage between your source table and your target XML File
2. Map your source fields in the stage
3. Add a new column on the stage, with the analytic function corresponding to the wanted sort as expression
4. Map this column to the repetition key of your XML Node



The expression used in the sortKey column here is:
Explanation:
This expression will tell SQL Server to generate a number for each row, with the order defined in the condition.
We are then using this "sorted" column on the XML Node repetition key.

Please refer to the documentation of your database to find the correct function / syntax to perform this.
For SQL Server: msdn.microsoft.com/en-us/library/ms186734.aspx

Hi,

Thanks for your answer.

That's exactly what I did because the CONCAT was too complicated in my case.