XML Syntax Error

Transact-SQL
1

Hi experts:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @currDate Date = GETDATE();
DECLARE @7DaysAgo Date = DateAdd(Day, -7, @currDate)

SET @xml = CAST((Select ParentOrgId AS 'td', '', ProNumber AS 'td', '', ParentPrimaryRef AS 'td', '', COUNT() AS 'Nbr of Dups Within Past 7 Days' AS 'td' <<--- Incorrect syntax near the keyword AS'
from shipments
where ParentOrgId = 128 and CreateDT >= @7DaysAgo
Group by ParentOrgId, ParentPrimaryRef, ProNumber
Having COUNT() > 1
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

I need for the report to show the number of Dups so how can I get around the error? Thanks

2

Suggest eliminating single quotes around object identifiers. Columns, tables, and aliases should use square brackets instead. Only use single quotes to delimit string literals.

3

Thanks for the tip, @robert_volk
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @currDate Date = GETDATE();
DECLARE @7DaysAgo Date = DateAdd(Day, -7, @currDate)

SET @xml = CAST((Select ParentOrgId AS [td], '', ProNumber AS [td], '', ParentPrimaryRef AS [td], '', COUNT() AS [Nbr of Dups Within Past 7 Days] AS [td] <<--- Incorrect syntax near the keyword AS'
from shipments
where ParentOrgId = 128 and CreateDT >= @7DaysAgo
Group by ParentOrgId, ParentPrimaryRef, ProNumber
Having COUNT() > 1
FOR XML PATH([tr]), ELEMENTS ) AS NVARCHAR(MAX));

i still get the same sytanx error

4

Ahh, I missed something. Still needs:

  1. COUNT() needs to be COUNT(*)

  2. PATH needs to be PATH('tr'), that syntax requires single quotes. Sorry I missed that earlier.

  3. AS [Nbr of Dups Within Past 7 Days] AS [td], this is double-aliasing, you just want TD.

5

All valid point,s @robert_volk but I still get the same error near AS td

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @currDate Date = GETDATE();
DECLARE @7DaysAgo Date = DateAdd(Day, -7, @currDate)

SET @xml = CAST((Select ParentOrgId AS [td], '', ProNumber AS [td], '', ParentPrimaryRef AS [td], '', COUNT() AS [Nbr of Dups Within Past 7 Days] AS td ---<<<< syntax error
from shipments
where ParentOrgId = 128 and CreateDT >= @7DaysAgo
Group by ParentOrgId, ParentPrimaryRef, ProNumber
Having COUNT() > 1
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

The Having COUNT(*) > 1 line IS correct, it contains a asterisk, the site editor is not showing it

6

COUNT() AS [Nbr of Dups Within Past 7 Days] AS td

needs to be:

COUNT(*) AS td

7

SET @xml = CAST((Select ParentOrgId AS [td], '', ProNumber AS [td], '', ParentPrimaryRef AS [td], '', COUNT(Asterisk) AS [Nbr of Dups Within Past 7 Days] AS td <<<< syntax error here
from shipments
where ParentOrgId = 128 and CreateDT >= @7DaysAgo
Group by ParentOrgId, ParentPrimaryRef, ProNumber
Having COUNT(Asterisk) > 1
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

8

COUNT(*) AS [Nbr of Dups Within Past 7 Days] td Error: incorrect syntax near td

9

Sorry, I don't know how else to say this:

YOU CANNOT HAVE

COUNT(*) AS [Nbr of Dups Within Past 7 Days] td

IT HAS TO BE:

COUNT(*) AS td

If you want it to show as a TD element in the generated XML.

10

I apologize for misinterpreting your post, @robert_volk. Thank you.

For those who are still interested... this is the final code:

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @currDate Date = GETDATE();
DECLARE @7DaysAgo Date = DateAdd(Day, -7, @currDate)

SET @xml = CAST((Select ParentOrgId AS [td], '', ProNumber AS [td], '', ParentPrimaryRef AS [td], '', COUNT() AS td
from shipments
where ParentOrgId = 128 and CreateDT >= @7DaysAgo
Group by ParentOrgId, ParentPrimaryRef, ProNumber
Having COUNT() > 1
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

SET @body ='

Duplicate Shipments in last 7 Days

'

SET @body = @body + @xml +'

Parent Org ID Pro Number Parent Primary Ref Nbr of Dups Within Past 7 Days
'