No Suggestions When FOR_XML used in Sub-Query

MikeyC

Another problem with no suggestions with certain code:

SELECT 
	EI.SampleField, 
	(SELECT EI_2.Part + ','
		FROM (SELECT 1 AS SampleField, 'Part 1' AS Part
						UNION ALL
						SELECT 1 AS SampleField, 'Part 3' AS Part
						UNION ALL
						SELECT 1 AS SampleField, 'Part 2' AS Part
						UNION ALL
						SELECT 2 AS SampleField, 'Part 1' AS Part
						UNION ALL
						SELECT 2 AS SampleField, 'Part 2' AS Part) EI_2
		WHERE 
			EI_2.SampleField = EI.SampleField
		ORDER BY 
			EI_2.Part
		FOR XML PATH(''), TYPE 
		).value('.[1]','varchar(max)') AS Sample_Rollup
FROM (SELECT 1 AS SampleField
			UNION ALL
			SELECT 2 AS SampleField) EI

If you go on line two and delete "SampleField" and hit ctrl-space you don't get any suggestions from the EI alias. (You also don't get any EI suggestion inside the FOR XML sub-query.)

The query is valid and runs, but it is like the SQL Prompt parser doesn't know how to deal with it.

This may sort of be related to another bug: http://www.red-gate.com/MessageBoard/viewtopic.php?t=10614

Another problem, when you put that in a CTE like:

WITH Rolled AS (SELECT 
   EI.SampleField, 
   (SELECT EI_2.Part + ',' 
      FROM (SELECT 1 AS SampleField, 'Part 1' AS Part 
                  UNION ALL 
                  SELECT 1 AS SampleField, 'Part 3' AS Part 
                  UNION ALL 
                  SELECT 1 AS SampleField, 'Part 2' AS Part 
                  UNION ALL 
                  SELECT 2 AS SampleField, 'Part 1' AS Part 
                  UNION ALL 
                  SELECT 2 AS SampleField, 'Part 2' AS Part) EI_2 
      WHERE 
         EI_2.SampleField = EI.SampleField 
      ORDER BY 
         EI_2.Part 
      FOR XML PATH(''), TYPE 
      ).value('.[1]','varchar(max)') AS Sample_Rollup 
FROM (SELECT 1 AS SampleField 
         UNION ALL 
         SELECT 2 AS SampleField) EI)
SELECT 
	*
FROM <Cursor>

And try to get suggestions at <Cursor> you don't get Rolled as a suggestion.

Anu D

Many thanks for your post and apologies for inconvenience caused.

You are correct it is the same issue I have already logged this in our internal tracking system whose id is SP-2925 and added your comments to it.

It will be reviewed by our development team for a future release of SQL Prompt although we have no timescales for this at present.

Kindly let us know if you have any issues or questions regarding the product,I'll like to help.

MikeyC

I'm not sure the second issue is the same issue as the first. Suggestions in the FROM normally work for CTE tables, but not if the CTE contains a FOR XML PATH query. Even in a much simpler query:

WITH Rolled AS (SELECT 
      (SELECT 'Test' 
      FOR XML PATH(''), TYPE 
      ).value('.[1]','varchar(max)') AS Sample_Rollup) 
SELECT 
   * 
FROM <CURSOR>