June 7, 2012 at 7:47 am
I'm actually posting a solution, not a question!
The reason for this post is because of the work I've carried out investigating the error message shown in the subject heading. I found a lot of solutions and workarounds that weren't suitable and decided to set up a test example to ask for assistance here. And as happens so often for me, the simplification and construction of the question helps me to find a solution.
So I thought I'd share it.
I have a Stored Procedure that returns XML data, which works perfectly fine. A VB.NET program passes a parameter to it and gets the returned XML data.
All is good.
Now a developer wanted to be able to call this Stored Procedure within another SP and pass the returned XML through to the new SP, which is where the fun began. He tried a variety of ways to assign an XML variable to the call of the SP without success. So I thought (as it only returns 1 XML record) that it could be inserted into a table with an XML column and then selected from that into an XML variable. See the following code for a simplified example:
CREATE TABLE #XML_Demo(
Att_IDVARCHAR(10),
Att_RefVARCHAR(10),
Att_AnswerVARCHAR(10),
Att_Ref2VARCHAR(10),
Att_SubINT
);
INSERT INTO #XML_Demo(Att_ID, Att_Ref, Att_Answer, Att_Ref2, Att_Sub)
SELECT '', '', '567', '1.2.3' , NULL
UNION ALL
SELECT'', '', '890', '4.5.6' , NULL
UNION ALL
SELECT'123', '12', '345', '6.7.8' , 1
UNION ALL
SELECT'123', '12', '567', '1.2.3' , 2
UNION ALL
SELECT'124', '34', '567', '4.5.6' , 3
UNION ALL
SELECT'', '', '567', '1.2.3' , 4
UNION ALL
SELECT'', '', '789', '4.5.6' , 5;
GO
CREATE PROC Test_Proc(@Identifier INT)
AS
SET NOCOUNT ON;
SELECT (SELECT 123 AS "TheVersion",
@Identifier AS "TheIdentifier"
FOR XML PATH('MainDataHeader'),TYPE),
(SELECT Att_Answer AS "@NullField1",
Att_Ref2 AS "@NullField2"
FROM #XML_Demo
WHERE Att_Sub IS NULL
FOR XML PATH('OtherAnswer'),ROOT('NullSubData'),TYPE),
(SELECT t1.Att_ID AS "@ID",
t1.Att_Ref AS "@Ref",
(SELECT t2.Att_Answer AS "@NullField1",
t2.Att_Ref2 AS "@NullField2"
FROM #XML_Demo t2
WHERE t2.Att_ID=t1.Att_ID
AND t2.Att_Ref=t1.Att_Ref
AND t2.Att_Sub IS NOT NULL
ORDER BY t2.Att_Sub
FOR XML PATH('OtherAnswer'),TYPE)
FROM #XML_Demo t1
WHERE t1.Att_Sub IS NOT NULL
GROUP BY t1.Att_ID,t1.Att_Ref
ORDER BY MAX(t1.Att_Sub)
FOR XML PATH('Other'),ROOT('OtherData'),TYPE)
FOR XML PATH('MainData');
SET NOCOUNT OFF;
GO
--exec Test_Proc 456;
DECLARE @XMLTable AS TABLE(XMLData XML);
DECLARE @XMLData XML;
INSERT INTO @XMLTable(XMLData)
exec Test_Proc 456;
SELECT @XMLData = XMLData FROM @XMLTable;
SELECT @XMLData;
DROP TABLE #XML_Demo
DROP PROC Test_Proc;
This resulted in the error message 'The FOR XML clause is not allowed in a INSERT statement'.
Some workarounds I have seen use OPENROWSET, but I can't guarantee that the user's system is set up to allow that.
Other systems altered the output from the SP so that it is in text format but structured like XML, then converted back into XML once it is stored in the required variable. This would work but would have a knock-on impact for the VB.NET software as it is currently expecting XML, not a string that just happens to look like it.
I then had a thought about the error message itself. It is complaining that the INSERT statement itself contains 'FOR XML'. So I thought what would happen if I removed the actual insert by a step - within the SP assigned the SELECT to a variable and then output that variable?
See the amended version:
CREATE TABLE #XML_Demo(
Att_IDVARCHAR(10),
Att_RefVARCHAR(10),
Att_AnswerVARCHAR(10),
Att_Ref2VARCHAR(10),
Att_SubINT
);
INSERT INTO #XML_Demo(Att_ID, Att_Ref, Att_Answer, Att_Ref2, Att_Sub)
SELECT '', '', '567', '1.2.3' , NULL
UNION ALL
SELECT'', '', '890', '4.5.6' , NULL
UNION ALL
SELECT'123', '12', '345', '6.7.8' , 1
UNION ALL
SELECT'123', '12', '567', '1.2.3' , 2
UNION ALL
SELECT'124', '34', '567', '4.5.6' , 3
UNION ALL
SELECT'', '', '567', '1.2.3' , 4
UNION ALL
SELECT'', '', '789', '4.5.6' , 5;
GO
CREATE PROC Test_Proc(@Identifier INT)
AS
SET NOCOUNT ON;
DECLARE @XML XML;
SET @XML =(
SELECT (SELECT 123 AS "TheVersion",
@Identifier AS "TheIdentifier"
FOR XML PATH('MainDataHeader'),TYPE),
(SELECT Att_Answer AS "@NullField1",
Att_Ref2 AS "@NullField2"
FROM #XML_Demo
WHERE Att_Sub IS NULL
FOR XML PATH('OtherAnswer'),ROOT('NullSubData'),TYPE),
(SELECT t1.Att_ID AS "@ID",
t1.Att_Ref AS "@Ref",
(SELECT t2.Att_Answer AS "@NullField1",
t2.Att_Ref2 AS "@NullField2"
FROM #XML_Demo t2
WHERE t2.Att_ID=t1.Att_ID
AND t2.Att_Ref=t1.Att_Ref
AND t2.Att_Sub IS NOT NULL
ORDER BY t2.Att_Sub
FOR XML PATH('OtherAnswer'),TYPE)
FROM #XML_Demo t1
WHERE t1.Att_Sub IS NOT NULL
GROUP BY t1.Att_ID,t1.Att_Ref
ORDER BY MAX(t1.Att_Sub)
FOR XML PATH('Other'),ROOT('OtherData'),TYPE)
FOR XML PATH('MainData'));
SELECT @XML;
SET NOCOUNT OFF;
GO
--exec Test_Proc 456;
DECLARE @XMLTable AS TABLE(XMLData XML);
DECLARE @XMLData XML;
INSERT INTO @XMLTable(XMLData)
exec Test_Proc 456;
SELECT @XMLData = XMLData FROM @XMLTable;
SELECT @XMLData;
DROP TABLE #XML_Demo
DROP PROC Test_Proc;
All I've done is add the variable @XML within the SP and used that as the output instead - and it works.
I haven't seen this method suggested anywhere else. It may not work for everyone but it works for my situation and I thought that sharing it may help somebody else out sometime in the future.
June 7, 2012 at 9:21 am
Interesting. I thought I would give you another alternative since you were only returning a single record (value).
I am posting your code plus the code I added. The new procedure uses an OUTPUT variable instead.
CREATE TABLE #XML_Demo(
Att_IDVARCHAR(10),
Att_RefVARCHAR(10),
Att_AnswerVARCHAR(10),
Att_Ref2VARCHAR(10),
Att_SubINT
);
INSERT INTO #XML_Demo(Att_ID, Att_Ref, Att_Answer, Att_Ref2, Att_Sub)
SELECT '', '', '567', '1.2.3' , NULL
UNION ALL
SELECT'', '', '890', '4.5.6' , NULL
UNION ALL
SELECT'123', '12', '345', '6.7.8' , 1
UNION ALL
SELECT'123', '12', '567', '1.2.3' , 2
UNION ALL
SELECT'124', '34', '567', '4.5.6' , 3
UNION ALL
SELECT'', '', '567', '1.2.3' , 4
UNION ALL
SELECT'', '', '789', '4.5.6' , 5;
GO
CREATE PROC Test_Proc(@Identifier INT)
AS
SET NOCOUNT ON;
DECLARE @XML XML;
SET @XML =(
SELECT (SELECT 123 AS "TheVersion",
@Identifier AS "TheIdentifier"
FOR XML PATH('MainDataHeader'),TYPE),
(SELECT Att_Answer AS "@NullField1",
Att_Ref2 AS "@NullField2"
FROM #XML_Demo
WHERE Att_Sub IS NULL
FOR XML PATH('OtherAnswer'),ROOT('NullSubData'),TYPE),
(SELECT t1.Att_ID AS "@ID",
t1.Att_Ref AS "@Ref",
(SELECT t2.Att_Answer AS "@NullField1",
t2.Att_Ref2 AS "@NullField2"
FROM #XML_Demo t2
WHERE t2.Att_ID=t1.Att_ID
AND t2.Att_Ref=t1.Att_Ref
AND t2.Att_Sub IS NOT NULL
ORDER BY t2.Att_Sub
FOR XML PATH('OtherAnswer'),TYPE)
FROM #XML_Demo t1
WHERE t1.Att_Sub IS NOT NULL
GROUP BY t1.Att_ID,t1.Att_Ref
ORDER BY MAX(t1.Att_Sub)
FOR XML PATH('Other'),ROOT('OtherData'),TYPE)
FOR XML PATH('MainData'));
SELECT @XML;
SET NOCOUNT OFF;
GO
CREATE PROC Test_Proc2(@Identifier INT, @XML XML OUTPUT)
AS
SET NOCOUNT ON;
SET @XML =(
SELECT (SELECT 123 AS "TheVersion",
@Identifier AS "TheIdentifier"
FOR XML PATH('MainDataHeader'),TYPE),
(SELECT Att_Answer AS "@NullField1",
Att_Ref2 AS "@NullField2"
FROM #XML_Demo
WHERE Att_Sub IS NULL
FOR XML PATH('OtherAnswer'),ROOT('NullSubData'),TYPE),
(SELECT t1.Att_ID AS "@ID",
t1.Att_Ref AS "@Ref",
(SELECT t2.Att_Answer AS "@NullField1",
t2.Att_Ref2 AS "@NullField2"
FROM #XML_Demo t2
WHERE t2.Att_ID=t1.Att_ID
AND t2.Att_Ref=t1.Att_Ref
AND t2.Att_Sub IS NOT NULL
ORDER BY t2.Att_Sub
FOR XML PATH('OtherAnswer'),TYPE)
FROM #XML_Demo t1
WHERE t1.Att_Sub IS NOT NULL
GROUP BY t1.Att_ID,t1.Att_Ref
ORDER BY MAX(t1.Att_Sub)
FOR XML PATH('Other'),ROOT('OtherData'),TYPE)
FOR XML PATH('MainData'));
SET NOCOUNT OFF;
GO
--exec Test_Proc 456;
DECLARE @XMLTable AS TABLE(XMLData XML);
DECLARE @XMLData XML;
INSERT INTO @XMLTable(XMLData)
exec Test_Proc 456;
SELECT @XMLData = XMLData FROM @XMLTable;
SELECT @XMLData;
GO
DECLARE @XMLData XML;
EXEC Test_Proc2 456, @XMLData OUTPUT;
SELECT @XMLData;
GO
DROP TABLE #XML_Demo
DROP PROC Test_Proc;
DROP PROC Test_Proc2;
June 7, 2012 at 9:28 am
Horribly obvious!
And yet it would appear I'm not the first to miss that, from the hunting around through the world of Google.
And you've just made me realise - we don't appear to use the OUTPUT clause very often where I work. I'll have to find out why - probably some strange historical cause.
Thanks for the code.
June 7, 2012 at 9:38 am
BrainDonor (6/7/2012)
Horribly obvious!And yet it would appear I'm not the first to miss that, from the hunting around through the world of Google.
And you've just made me realise - we don't appear to use the OUTPUT clause very often where I work. I'll have to find out why - probably some strange historical cause.
Thanks for the code.
Your welcome.
June 7, 2012 at 9:41 am
Have you tried a UDF instead of an SP for this? Might be easier.
Like so:
IF OBJECT_ID(N'dbo.XMLTest') IS NOT NULL
DROP FUNCTION dbo.XMLTest ;
GO
CREATE FUNCTION dbo.XMLTest (@Var_in INT)
RETURNS XML
AS
BEGIN
RETURN (SELECT Number FROM Common.dbo.Numbers AS Numbers WHERE Number <= @Var_in FOR XML AUTO, TYPE) ;
END ;
GO
SELECT dbo.XMLTest(10) ; -- "Normal" select for scalar UDF
GO
DECLARE @X XML ;
EXEC @X = dbo.XMLTest @Var_in = 10 ; -- Exec used on scalar UDF (not as commonly known as Select for them)
SELECT @X ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply