xml from stored procedure into temp table

Question

xml from stored procedure into temp table

jeff ernest

SSC Veteran

Points: 245
More actions
June 17, 2005 at 1:36 pm

#167500

I have a stored procedure ( lets call it "A" ) that returns some data from the database in xml, that is called from a vb application, that I don't want to modify. It does not use an output parameter, so I am trying to figure out a way to call stored procedure "A" from a new stored procedure "B", and get the result (xml formtated) into another table, or a variable, or anthing I can use.
I tried:
create table #temp1( prod varchar(4000))
insert #temp1(prod)
EXEC A "var1", "var2
but that kicks out the error "FOR XML AUTO is not allowed in an INSERT statement"
Any ideas, or can what I'm trying not be done ?
June 20, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Parks Old Hand Points: 364 More actions · Answer 1

If proc B receives the return from proc A as an XML string, how about (you'll need to adjust for the what your XML looks like):

SELECT @xmlString = EXEC A "var1", "var2"

DECLARE @idoc INT

DECLARE @Xpath

SET @Xpath = '/root-node/children'

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

INSERT INTO #temp1

SELECT fname from OPENXML (@idoc, @Xpath, 1)

with (attribute datatype)

EXEC sp_xml_removedocument @idoc

jeff ernest SSC Veteran Points: 245 More actions · Answer 2

I tried your suggestion, replacing the "a" and var1, and var2 with my own, and can not get past the first line. For some reason it keeps balking at the "exec" command. However when i just execute the line from the "exec" forward that returns an xml string, I just can't seem to get it into a variable. Thanks for the reply though.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

First to run more than one stored proc you need sp_executesql run a search for it in the BOL(books online), second SQL Server XML is very limited so you have to use .NET XML classes to do complex things. I could be wrong but you can use Excel XP/2003. Try the links below for some sample code to get started. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/Office_Excel_XML_Toolbox.asp

http://xmlfiles.com/articles/sample_chapters/sams_xmlforaspnet/default.asp

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie