November 5, 2010 at 12:44 pm
I have a stored procedure that genereates a resultset of 1 column with xml data.
Now, I am trying to call that stored procedure in a view or another stored procedure and convert the xml into columns but I keep getting the error message below. Any help or assistance is truly appreciated.
********************************sample script**********************
SET NOCOUNT ON
DECLARE @StudentID INT
SET @StudentID = 123
IF (OBJECT_ID('tempdb..#test') IS NOT NULL)
DROP TABLE #test
DECLARE @xmlholder TABLE
(
xmlcolumn xml
)
CREATE TABLE #test
(
Identifier INT
,BatchNumber VARCHAR(50)
)
INSERT INTO #@xmlholder(xmlcolumn)
EXEC sp_called @StudentID
/*
data from sp_called looks like below:-
<Cust>
<Transaction>
<Identifier>Test1</Identifier>
<BatchNumber>BATCH1 </BatchNumber>
</Transaction>
</Cust>
*/
INSERT INTO #test(Identifier,BatchNumber)
SELECT ParamValues.[Transaction].value('Identifier[1]', 'VARCHAR(20)') AS Identifier
,ParamValues.[Transaction].value('BatchNumber[1]', 'VARCHAR(50)') AS BatchNumber
FROM @xmlholder
CROSS APPLY xmlcolumn.nodes('//Cust/Transaction') AS ParamValues([Transaction])
SELECT DISTINCT Identifier
,BatchNumber
FROM #test
DROP TABLE #test
SET NOCOUNT OFF
*********************************************************
Msg 6819, Level 16, State 5, Procedure up_APUSIX_StudentRecord_StudentLedger, Line 712
The FOR XML clause is not allowed in a INSERT statement.
November 17, 2010 at 3:34 pm
After doing some recheck and online research, we found that adding the TYPE syntax in the SQL script that creates the xml column and that did it.
Thanks:-D
November 17, 2010 at 3:57 pm
Thank you for posting the solution you found.
Much better than a "never mind, I got it" reply, or, even worse, none at all... 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply