At a guess -
FOR XML PATH are being used to build an aggregated string out of the inner query - specifically, to build a column list for use in a later dynamic query. MySQL uses backticks (`) to "quote" DB object names, and there's a GROUP_CONCAT aggregate function built-in.
CROSS APPLY isn't actually using anything from the query to determine its values - it appears to be a hard-coded sub-query, in which case you should be able to just put it in as a
CROSS JOIN instead.
You can search this site or use your favorite search engine to find out how to do row numbers in MySQL. In fact, here's an answer I provided a while ago that shows you how to generate a row number that would've been done with a
PARTITION BY in MS SQL.
Basically, you set up a variable to hold the row number. For each row, the variable's starting value will be the number of the previous row. You also store the current value of each partition column. If any of the partition columns change, set the row number variable to 1, otherwise, add one to it. Note that the query (or subquery) you generate the row number in must be sorted first by all the partition columns, then by the columns that would be in the
ORDER BY using the MS SQL functions.
The one thing I can't see a way around easily is your use of a global temporary table (
##NewGeneric). Technically that's not really a part of this code (it assumes it already exists and just uses it), but it's something you should at least be aware of. In MS SQL Server, a global temp table is created in one session, goes away when that session either drops it or closes (mostly), but allows other sessions to access it (and, if another session is using it when it should go away, it holds off until that session's current statement (transaction?) is over).
If you are really having other sessions access it, you'll have to use a regular table. The biggest differences should be that you have to be sure to drop it yourself, and you won't be able to drop it immediately if it's involved in a current transaction; your session will have to wait around until it's free to drop it.