June 5, 2003 at 8:58 am
There is a source sql:
select Prod_Date from Prod_Date Prod_Dates for xml raw, elements
that returns following result:
<Prod_Dates Prod_Date="5/31/2002"/>
<Prod_Dates Prod_Date="6/30/2002"/>
<Prod_Dates Prod_Date="7/31/2002"/>
<Prod_Dates Prod_Date="8/31/2002"/>
There is another sp that takes xml string (varchar(8000)) as a parameter:
exec mysp '<Prod_Dates Prod_Date="5/31/2002"/>
<Prod_Dates Prod_Date="6/30/2002"/>
<Prod_Dates Prod_Date="7/31/2002"/>
<Prod_Dates Prod_Date="8/31/2002"/>'
Is there a way to capture results of the source sql statement so it can be passed as an XML string parameter to second sp?
Note: "assignment", "insert into", or "select ... into" does not work with "for xml".
June 5, 2003 at 2:05 pm
I have a modified version of a Ken Henderson proc that will take a sql statement with for xml auto and put the results into a text field in a table. It could be modified to do what you need. You would need to be sure that your output would fit into a varchar(8000) since text parameters are only available as input parameters to procs. Or you could just put it into a temp table and have the other proc pull from the temp table.
June 6, 2003 at 7:57 am
could you please post your generic proc.. I would greatly appreciate that..
thanks
-vishy
June 6, 2003 at 10:23 am
Here it is. I am not sure how the formatting will hold up.
IF OBJECT_ID('sp_run_xml_proc','P') IS NOT NULL
DROP PROC sp_run_xml_proc
GO
CREATE PROC sp_run_xml_proc
@sql varchar(8000) -- Proc to run
AS
DECLARE @dbname sysname,
@sqlobject int, -- SQL Server object
@object int, -- Work variable for accessing COM objects
@hr int, -- Contains HRESULT returned by COM
@results int, -- QueryResults object
@msgs varchar(8000) -- Query messages
IF (@sql='/?') GOTO Help
-- Create a SQLServer object
EXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @sqlobject OUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @sqlobject, @hr
RETURN
END
-- Set SQLServer object to use a trusted connection
EXEC @hr = sp_OASetProperty @sqlobject, 'LoginSecure', 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @sqlobject, @hr
RETURN
END
-- Turn off ODBC prefixes on messages
EXEC @hr = sp_OASetProperty @sqlobject, 'ODBCPrefix', 0
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @sqlobject, @hr
RETURN
END
-- Open a new connection (assumes a trusted connection)
EXEC @hr = sp_OAMethod @sqlobject, 'Connect', NULL, @@SERVERNAME
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @sqlobject, @hr
RETURN
END
-- Get a pointer to the SQLServer object's Databases collection
EXEC @hr = sp_OAGetProperty @sqlobject, 'Databases', @object OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @sqlobject, @hr
RETURN
END
-- Get a pointer from the Databases collection for the current database
SET @dbname=DB_NAME()
EXEC @hr = sp_OAMethod @object, 'Item', @object OUT, @dbname
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Call the Database object's ExecuteWithResultsAndMessages2 method to run
the proc
EXEC @hr = sp_OAMethod @object, 'ExecuteWithResultsAndMessages2',@results
OUT, @sql, @msgs OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Display any messages returned by the proc
PRINT @msgs
DECLARE @rows int, @cols int, @x int, @y int, @col varchar(8000), @row
varchar(8000)
-- Call the QueryResult object's Rows method to get the number of rows in
the result set
EXEC @hr = sp_OAMethod @results, 'Rows',@rows OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Call the QueryResult object's Columns method to get the number of columns
in the result set
EXEC @hr = sp_OAMethod @results, 'Columns',@cols OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
--DECLARE @table TABLE (XMLText text)
-- Create a temporary table instead.
Create Table #TempXML (XMLText ntext)
-- Put a bogus record in there.
Insert Into #TempXML (XMLText) Values ('')
-- Get a pointer to the text field.
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(XMLText)
From #TempXML
-- Retrieve the result set column-by-column using the GetColumnString method
SET @y=1
WHILE (@y<=@rows)
BEGIN
SET @x=1
SET @row=''
WHILE (@x<=@cols)
BEGIN
EXEC @hr = sp_OAMethod @results, 'GetColumnString',@col OUT, @y, @x
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
SET @row=@row+@col
SET @x=@x+1
END
-- INSERT @table VALUES (@row)
-- Stuff the data into the temporary table instead of the local variable of
table type.
UpdateText #TempXML.XMLText @ptrval NULL 0 @row
SET @y=@y+1
END
SELECT * FROM #TempXML
EXEC sp_OADestroy @sqlobject -- For cleanliness
DROP TABLE #TempXML
RETURN 0
Help:
PRINT 'You must specify a procedure name to run'
RETURN -1
GO
EXEC sp_run_xml_proc 'SELECT * FROM pubs..authors as author
FOR XML AUTO'
Edited by - tkbr0wn on 06/06/2003 10:23:39 AM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply