Storing the output of a FOR XML EXPLICIT in a table?
-
Hi all,
I am trying to store the output of a Select statement with a FOR XML EXPLICIT back into the dbase. Is there any way of doing this? Here is a simplistic view of the SQL on my attempt. Thanks
CREATE TABLE #Members (UserNo int NOT NULL PRIMARY KEY, Phone varchar(50) NOT NULL, Email varchar(50) NOT NULL, FullName varchar(50) NOT NULL)
INSERT INTO #Members values (1, '+27 11 686 6820','test@whatever.co.za','Giles whoever Blarney')
CREATE TABLE #MemberView (UserNo int NOT NULL PRIMARY KEY, Personal varchar(2000) NULL)
INSERT INTO #MemberView (UserNo) VALUES (1)
UPDATE #MemberView
SET Personal = (
SELECT 1 as Tag,
NULL as Parent,
Phone as [section0!1!phone!element],
Email as [section0!1!email!element],
FullName as [section0!1!name!element]
FROM #Members
WHERE UserNo = 1
FOR XML EXPLICIT
 
WHERE UserNo = 1
-
Looks like I found the answer to my own question. This is from this site http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=16 - if you go down to the "sp_run_xml_proc" section... This proc will return your XML stream like a normal string, so I can do my inserting to the temp table with no problem. I made one change to the sp - changed the parameter from a "sysname" to a "varchar" so I can pass parameters... Thought I'd pass it along in case it helps anybody else.
USE master
GO
IF OBJECT_ID('sp_run_xml_proc','P') IS NOT NULL
DROP PROC sp_run_xml_proc
GO
CREATE PROC sp_run_xml_proc
@procname varchar(100) -- 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 (@procname='/?') 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, @procname, @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 varchar(8000))
-- 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)
SET @y=@y+1
END
SELECT * FROM @table
EXEC sp_OADestroy @sqlobject -- For cleanliness
RETURN 0
Help:
PRINT 'You must specify a procedure name to run'
RETURN -1
GO
-
Thanks , thats brilliant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply