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

     &nbsp

    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