locked
creating a file(may be xml) in project directory from mysql trigger RRS feed

  • Question

  • User-282339866 posted

    i have a reqirement of creating an xml file in a particular directory when a new row is inserted in a particular table.

    please help

    thanku

    Saturday, February 4, 2012 2:23 AM

All replies

  • User1950091109 posted
    Simply you can create the trigger on the table you choose, these write your file lines as you wish. Here it is, i give you an example how to use the cmd to write the file. Ofcourse the script needs an enough permission to do that.
    CREATE TRIGGER [LogTable] ON [dbo].[ChrisTable] 
    FOR INSERT, UPDATE
    AS
    --Declare your vars.
    DECLARE @C1Value varchar(1000),
      @C2Value varchar(1000),
      @data varchar(1000),
      @cmd varchar(1000),
           @Filename varchar(128)

    --Set LogFile to Use
    SET @Filename = 'C:\Logfile.txt'

    --No Messages needed for output.
    SET NOCOUNT ON

    --This will delete the old log. Remove this if you want it to append to the log.
    SET @cmd = 'DEL ' + @Filename
    EXEC master..xp_cmdshell @cmd

    --Create a cursor to run through the table.
    DECLARE data_cursor CURSOR FOR 

    --Specify the Columns you want to record.
    SELECT Column1, Column2
    FROM ChrisTable
    OPEN data_cursor
    FETCH NEXT FROM data_cursor 

    --Vars to Hold Column Values.
    INTO @C1Value, @C2Value

    --Start Cursor
    WHILE @@FETCH_STATUS = 0
    BEGIN

    --Comma Seperate the Values.
    SET @data = @C1Value + ', ' + @C2Value

    --Using the cmdshell just ECHO the value to a file.
    SET @cmd = 'echo ' + @data + ' >> ' + @Filename
    EXEC master..xp_cmdshell @cmd

    --Next Row...
    FETCH NEXT FROM data_cursor 
    INTO @C1Value, @C2Value
    END

    --Add a line break at end. 
    SET @cmd = 'echo --------------- >> ' + @Filename
    EXEC master..xp_cmdshell @cmd

    --Close and Deallocate Cursor.
    CLOSE data_cursor
    DEALLOCATE data_cursor
    Saturday, February 4, 2012 2:32 AM
  • User1950091109 posted

    In case XML you can save the output of this query and write it to the file in the same way

    SELECT SELECT 'White' AS Color1,
    'Blue' AS Color2,
    'Black' AS Color3,
    'Light' AS 'Color4/@Special',
    'Green' AS Color4,
    'Red' AS Color5
    FOR
    XML PATH('Colors'),
    TYPE
    ),
    SELECT 'Apple' AS Fruits1,
    'Pineapple' AS Fruits2,
    'Grapes' AS Fruits3,
    'Melon' AS Fruits4
    FOR
    XML PATH('Fruits'),
    TYPE
    )
    FOR XML PATH(''),
    ROOT('SampleXML')
    GO

    -------------------------------------


    The output like that


    <SampleXML>
    <Colors>
    <Color1>White</Color1>
    <Color2>Blue</Color2>
    <Color3>Black</Color3>
    <Color4 Special="Light">Green</Color4>
    <Color5>Red</Color5>
    </Colors>
    <Fruits>
    <Fruits1>Apple</Fruits1>
    <Fruits2>Pineapple</Fruits2>
    <Fruits3>Grapes</Fruits3>
    <Fruits4>Melon</Fruits4>
    </Fruits>
    </SampleXML>

    Saturday, February 4, 2012 2:40 AM
  • User-282339866 posted

    EXEC MASTER.dbo.Xp_create_subdir 'C:\testForlder'

    the statement creates a folder named testFolder when i execute it in sqlserver,. but i want to do it in MYSQL

    please help 

    thanku

    Saturday, February 4, 2012 5:14 AM