locked
[Help] :How to insert an XML output of one query to another table RRS feed

  • Question

  • Hi,

    I am writing a stored procedure in which I am creating a dynamic SQL query and want to store it's output in an XML column of another table (i.e. log).A psuedo code of my query is below:-

    EXEC (@strSelect1+ ','+@strSelect2 + 'Into #Audit FROM #S JOIN #T' + @strOnClause + @strWhereClause )

    I want to insert the execution result of the above query to a column of another table for logging purpose but FOR XML is not allowed with "insert into" or "select into" predicate. Can anyone help find out how this can be achieved.

    An early response would be highly appreciated.

    Thanks,

    Ashish


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    Monday, November 12, 2012 10:29 AM

Answers

  • Try this:

    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
    CREATE TABLE #tmp ( yourXML XML )
    GO
    
    DECLARE @sql NVARCHAR(MAX) = 'SELECT *
    FROM sys.objects
    FOR XML RAW, TYPE'
    
    INSERT #tmp
    EXEC (@sql)

    NB: I can get the same error 'The FOR XML clause is not allowed in a INSERT statement' (sic) if I do not use the TYPE keyword.

    • Proposed as answer by Naomi N Monday, November 12, 2012 12:58 PM
    • Marked as answer by Iric Wen Tuesday, November 20, 2012 9:25 AM
    Monday, November 12, 2012 12:08 PM

All replies

  • Hi Ashish,

    --sample data
    CREATE TABLE #TEST(Col1 INT IDENTITY(1,1),Col2 XML)
    INSERT INTO #TEST (Col2) SELECT '<STUDENTINFO>
      <student ID="1" name="Sathya">
        <subject ID="1" Name="Electronics and Communication" />
        <subject ID="2" Name="Circuit Analysis" />
        <subject ID="3" Name="Mobile Communication" />
      </student>
      <student ID="2" name="Deepak">
        <subject ID="4" Name="Data Structure" />
        <subject ID="5" Name="Java" />
        <subject ID="6" Name="Database Management System" />
      </student>
      <student ID="3" name="sathish">
        <subject ID="7" Name="Soil Mechanics" />
        <subject ID="8" Name="Steel Design" />
        <subject ID="9" Name="Concrete Design" />
      </student>
    </STUDENTINFO>'
    
    
    --procedure returning XML
    CREATE PROCEDURE TEST_XML
    AS
    BEGIN
    DECLARE @Query1 VARCHAR(2000)
    DECLARE @Query2 VARCHAR(2000)
    SET @Query1 = 'SELECT' + '('
    SET @Query2 = 'SELECT Col2 FROM #TEST FOR XML AUTO,ELEMENTS)'
       EXEC (@Query1 + @Query2)   
    END
    
    --inserting procedure result into temp table 
    DECLARE @TEST TABLE (Col1 XML)
    INSERT @TEST
    EXEC TEST_XML
    SELECT col1.query('//STUDENTINFO') FROM @TEST


    Thanks & Regards, sathya

    Monday, November 12, 2012 11:31 AM
  • Try this:

    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
    CREATE TABLE #tmp ( yourXML XML )
    GO
    
    DECLARE @sql NVARCHAR(MAX) = 'SELECT *
    FROM sys.objects
    FOR XML RAW, TYPE'
    
    INSERT #tmp
    EXEC (@sql)

    NB: I can get the same error 'The FOR XML clause is not allowed in a INSERT statement' (sic) if I do not use the TYPE keyword.

    • Proposed as answer by Naomi N Monday, November 12, 2012 12:58 PM
    • Marked as answer by Iric Wen Tuesday, November 20, 2012 9:25 AM
    Monday, November 12, 2012 12:08 PM
  • Hi,

    Take a look on below link:

    http://www.mssqltips.com/sqlservertip/2118/scripts-to-use-xml-to-insert-and-update-rows-in-a-sql-server-table/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, November 12, 2012 12:12 PM
  • Thanks to all of you for your answers. I will work out on these options and will let you know the conclusion.

    Thanks again,

    Ashish


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    Monday, November 12, 2012 12:31 PM
  • CREATE TABLE TEST_TABLE(Col1 INT IDENTITY(1,1),Col2 VARCHAR(20))
    INSERT TEST_TABLE SELECT 'SQL Server 2000'
    INSERT TEST_TABLE SELECT 'SQL Server 2005'
    INSERT TEST_TABLE SELECT 'SQL Server 2008'
    INSERT TEST_TABLE SELECT 'SQL Server 2008R2'
    INSERT TEST_TABLE SELECT 'SQL Server 2012'
    --SELECT * FROM TEST_TABLE
    --SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE
    INSERT TEST_XML SELECT(SELECT * FROM TEST_TABLE FOR XML AUTO)
    SELECT * FROM TEST_XML


    Thanks & Regards, sathya

    Monday, November 12, 2012 5:08 PM