[solved] Wrapping SQL-query into XML PATH and Incorrect syntax error

Question

Hi guys! Since SW still can't provide a convenient way to use sql/swql-queries with email actions, we have to use hacks like that (thank you, mesverrum ) 
 both issues were solved 
 #1 So we had this SWQL query to gather some components info: 
 SELECT
n.Caption AS Node,
ccs.ApplicationID,
a.Name AS AppName,
ccs.ComponentID,
c.ComponentName,
cs.ErrorMessage AS Message,
cs.ComponentStatisticData AS Statistic
FROM Orion.APM.Component(nolock=true) c
JOIN Orion.APM.CurrentComponentStatus(nolock=true) ccs ON c.ComponentID = ccs.ComponentID
JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID
JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID
JOIN ORION.APM.CurrentStatistics(nolock=true) cs ON c.ComponentID = cs.ComponentID
WHERE ApplicationID = '1063' AND ComponentName like '%DSA%' 
 Which was successfully converted to SQL: 
 SELECT
n.Caption AS Node,
ccs.ApplicationID,
a.Name AS AppName,
ccs.ComponentID,
c.ShortName,
cs.ErrorMessage AS Message,
cs.ComponentStatisticData AS Statistic
FROM APM_Component c
JOIN APM_CurrentComponentStatus ccs ON c.ID = ccs.ComponentID
JOIN APM_Application a ON c.ApplicationID = a.ID
JOIN Nodes n ON a.NodeID = n.NodeID
JOIN APM_CurrentStatistics cs ON c.ID = cs.ComponentID
WHERE ccs.ApplicationID = '1063' AND c.ShortName like '%DSA%' 
 For me problem is to wrap it into XML PATH, there are a lot of " Invalid column name " errors in the DB manager, obviously I did something wrong: 
 
 #2 
 Anyway, after simulation, SW shows me another error: " MACRO SQL ERROR - Incorrect syntax near ')' ", don't even know why, I use this code: 
 ${SQL: SELECT ISNULL((
SELECT 
n.Caption AS Node,
ccs.ApplicationID,
a.Name AS AppName,
ccs.ComponentID, 
c.ShortName, 
cs.ErrorMessage AS Message,
cs.ComponentStatisticData AS Statistic
FROM APM_Component c 
JOIN APM_CurrentComponentStatus ccs ON c.ID = ccs.ComponentID 
JOIN APM_Application a ON c.ApplicationID = a.ID
JOIN Nodes n ON a.NodeID = n.NodeID
JOIN APM_CurrentStatistics cs ON c.ID = cs.ComponentID
WHERE ccs.ApplicationID = ${N=SwisEntity;M=ApplicationID} 
AND c.ShortName like '%DSA%'
FOR XML PATH('')),'None')} 
 
 Same code works fine in the DB Manager: 
 
 Could you help me please?

omavel · Accepted Answer

Shame on me guys, I was so clumsy, of course table names must be without those square brackets. 
 Fixed: 
 SELECT ISNULL((
SELECT
cast(cast(n.Caption as nvarchar(max)) + '' as XML),
cast(cast(ccs.ApplicationID as nvarchar(max)) + '' as XML),
cast(cast(a.Name as nvarchar(max)) + '' as XML),
cast(cast(ccs.ComponentID as nvarchar(max)) + '' as XML),
cast(cast(c.ShortName as nvarchar(max)) + '' as XML),
cast(cast(cs.ErrorMessage as nvarchar(max)) + '' as XML),
cast(cast(cs.ComponentStatisticData as nvarchar(max)) + '' as XML)
FROM APM_Component c
JOIN APM_CurrentComponentStatus ccs ON c.ID = ccs.ComponentID
JOIN APM_Application a ON c.ApplicationID = a.ID
JOIN Nodes n ON a.NodeID = n.NodeID
JOIN APM_CurrentStatistics cs ON c.ID = cs.ComponentID
WHERE ccs.ApplicationID = 1030
AND c.ShortName like '%DSA%'
FOR XML PATH('')),'None') 
 But the second issue is still actual

[solved] Wrapping SQL-query into XML PATH and Incorrect syntax error

Top Replies