Results of an XML stored proc to a variable
-
Hi all... I've got a stored procedure that returns XML. It's a template that gives me the basis for an XML string that I'm going to manipulate within a stored procedure (by filling in elements, etc). I need to call this stored procedure from another stored procedure, put it into a string, make some changes, then put it into another table as a varchar. I'm having a hard time getting it into a variable. I tried something like this:
create procedure xml_Test AS
select * from authors where au_lname = 'White' for xml auto
go
create table #Author (XMLString varchar(8000))
INSERT INTO #Author exec xml_test
DECLARE @XMLString varchar(8000)
SELECT @XMLString = XMLString FROM #Author
but it tells me:
The FOR XML clause is not allowed in a INSERT statement.
Any ideas of how I can get the results from the SELECT in the first stored procedure into a variable in the second?? Thanks for any help you can give!
-
There is a little note in BOL "Guidelines for Using the FOR XML Clause:" Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft SQL Server 2000 client.
That suggests that maybe one cannot do what you want.
Russ
Russel Loski, MCSE Business Intelligence, Data Platform
-
You can do it with something like:
INSERT INTO #Author
SELECT a.*
FROM OPENROWSET('SQLOLEDB','localhost';'sa';'abominable',
'SET FMTONLY OFF; exec pubs.dbo.xml_test;') AS a
But it will end up in binary because XML AUTO returns a binary stream. You could then cast it to nvarchar I suppose but it won't stay valid XML. Mayby you could save it to disk and try reading it again with a different method...
In any case, I wouldn't recommend it in production
If you absolutely want to do it server side, build a component and use sp_OA procedures
eric
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply