May 25, 2016 at 10:23 am
I'm running a query which returns results fine when run alone but when I try to put it into an XML variable I get the following error:
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Here is the query I'm running:
DECLARE @rssbody XML
SET @rssbody = (
select 'Fl01' as FLDB, PROC_SET_CODE, PROC_SET_NAME
from [Fl01].[dbo].[PRSM]
Where PROC_SET_CODE != 'SYSTEM'
Union
select 'Fl02' as FLDB, PROC_SET_CODE, PROC_SET_NAME from [Fl02].[dbo].[PRSM]
Where PROC_SET_CODE != 'SYSTEM'
Union
select 'Fl03' as FLDB, PROC_SET_CODE, PROC_SET_NAME from [Fl03].[dbo].[PRSM]
Where PROC_SET_CODE != 'SYSTEM'
)
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
May 25, 2016 at 10:35 am
You need to wrap the subquery with another select and use FOR XML
like this
DECLARE @rssbody XML;
SET @rssbody = (
SELECT FLDB,PROC_SET_CODE,PROC_SET_NAME FROM (
SELECT 'Fl01' as FLDB, PROC_SET_CODE, PROC_SET_NAME
FROM [Fl01].[dbo].[PRSM]
WHERE PROC_SET_CODE != 'SYSTEM'
UNION
SELECT 'Fl02' as FLDB, PROC_SET_CODE, PROC_SET_NAME
FROM [Fl02].[dbo].[PRSM]
WHERE PROC_SET_CODE != 'SYSTEM'
UNION
SELECT 'Fl03' as FLDB, PROC_SET_CODE, PROC_SET_NAME
FROM [Fl03].[dbo].[PRSM]
WHERE PROC_SET_CODE != 'SYSTEM'
) a
FOR XML AUTO
);
Far away is close at hand in the images of elsewhere.
Anon.
May 25, 2016 at 12:32 pm
That did it. Thanks David!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply