Click here to Skip to main content
15,909,747 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hello,
Can someone explain to me how to execute a stored procedure that yields output that is XML and then take that XML output and insert it into the table I've declared?

e.g.
SQL
DECLARE @cache TABLE (
val1 varchar(max)
val2 varchar(max)
val3 varchar(max)
)
INSERT @cache EXECUTE usp_Yoursp @Id=123
SELECT * FROM @cache

The SP "usp_Yoursp" yields some XML that I am trying to insert into @cache, but it fails because "Column name or number of supplied values does not match table definition". This is because I am trying to directly insert some XML into a table. How can I properly go about doing this?
Posted
Updated 21-Aug-13 3:20am
v3
Comments
Zoltán Zörgő 21-Aug-13 10:05am    
How exactly is that yield made: select, output parameter? Does it yield a dingle xml value (your table has three fields)?
iCanDivideByZero 21-Aug-13 10:06am    
Output parameter...it yields a single xml value e.g. "<row ....="</xml>" col2="" col1="">
Also the "ROW" has three fields, that is why I put three in the table.
Zoltán Zörgő 22-Aug-13 2:13am    
Still not clear. Could you make a short stored procedure that yields the result in the same way as your original one does? The exact logic is not needed, and of course some dummy data is enough, but the sp should work and should return the data as the original one return the data.
Maciej Los 21-Aug-13 14:54pm    

1 solution

In past I have used this way(using sp_xml_preparedocument)

Process XML using SQL in SQL Server[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900