CLR procedure to export xml file ?

  • hello everyone

    im new here and newbi about CLR , i need ur help about how to export XML file to a folder in the server through CLR bcuz my attempt with xp_cmdshell has failed i could create the file but its doesnt match my requirement , so im gonna try with CLR i just need to understand the concepts of how it works

    which environment im gonna use , and any help would be appreciated 🙂

  • There are many better ways (IMHO) to get XML from a database to disk than from within a T-SQL context. Have you exhausted all other options? What is your client language? Writing to the file system from a T-SQL context (xp_cmdshell and SQLCLR originate from a T-SQL context) is ill-advised for too many reasons to list here. Using SQLCLR will not allow you mark the assembly as SAFE, a red flag that you should look for alternatives.

    What will be invoking the process that exports XML? A user pushing a button on a front-end somewhere? A batch process?

    bcp.exe can extract xml columns to disk.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I'm not exactly expert at CLR, but I've bumbled my way through writing a text file using VB.NET's file operations just by digging through Bing searches on the subject. It's not that hard to do. I can't remember how I did it (was 3 years ago), but I remember it not being particularly difficult. Writing an XML file should be easy enough, unless you have oddball requirements.

    Have you thought about simply having SSIS export the file instead? That'll be even easier.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Totally untested, I'm not at my desk any more.

    using Microsoft.SqlServer.Server;

    using System.Data.SqlTypes;

    using System;

    using System.IO;

    namespace XMLWriter

    {

    public class XMLWriter

    {

    [SqlFunction]

    public static SqlString ReplaceMatch(

    SqlString inputXMLString,

    SqlString outputFileDIR)

    {

    try

    {

    // input parameters must not be NULL

    if (!inputXMLString.IsNull &&

    !outputFileDIR.IsNull)

    {

    System.IO.StreamWriter file = new System.IO.StreamWriter(outputFileDIR);

    file.WriteLine(inputXMLString);

    file.Close();

    }

    else

    // if any input paramater is NULL, return NULL

    return SqlString.Null;

    }

    catch

    {

    // on any error, return NULL

    return SqlString.Null;

    }

    return new SqlString();

    }

    };

    }


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • blah...

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • hello everyone

    i appreciate yr comments sorry for being late to reply

    well , i havent tried any option yet , other than bcp command and here the code did it a month a go but could go will tell u the reason :

    code on sqlserver:

    ALTER trigger [dbo].[trig1]

    ON [dbo].[t3] after INSERT

    AS

    BEGIN

    declare @id AS VARCHAR(6)

    -- this chose the prev last rec >> set @id = (select id from r7..t3 where id = (select max(id) from r7..t3) )

    set @id = (select id from t3 where id=(select max(id) -1 from t3) )

    --set @id = (Select top 1 id from r7..t3 order by ID desc)

    declare @FileName VARCHAR(8000)

    DECLARE @SQLCmd VARCHAR(500)

    declare @time VARCHAR(50)

    declare @date VARCHAR(50)

    set @time = replace(convert(varchar, getdate(), 108) ,':',';')

    set @date= (convert(varchar,getdate(),105))

    set @FileName = 'C:\Temp\'+@date+'_'+@time+'.xml'

    set @SQLCmd = 'bcp ' +

    '"SELECT c1 AS column1 ,c2 AS column2 FROM table as patientinfo WHERE id=' + @id +

    ' FOR XML auto,elements, ROOT(''xml'') "' +

    ' queryout ' +

    @FileName +

    ' -w -T -S computername\sqlinstance'

    SELECT @SQLCmd AS 'Command to execute'

    EXECUTE master..xp_cmdshell @SQLCmd

    END

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

    ok my idea is that im makin a trigger that export xml file for the last inserted recod , for example i inserted a record , immediatly i want the xml containg the records feild in a specific folder on a the server i could make the xml file but i couldnt find a way to tell the trigger the inserted record bcuzi guess the trigger locks it , until i go insert another record , i could export any record in the table but not the last inserted , i hope my idea is clear

    i didnt tried anything else ssis or CLR cuz im tryin to do it , i tried to stay away from coding (CLR) cuz im not that good but let me know wat do u think guys ?

    appreciated 🙂

  • opc.three (12/21/2011)


    There are many better ways (IMHO) to get XML from a database to disk than from within a T-SQL context. Have you exhausted all other options? What is your client language? Writing to the file system from a T-SQL context (xp_cmdshell and SQLCLR originate from a T-SQL context) is ill-advised for too many reasons to list here. Using SQLCLR will not allow you mark the assembly as SAFE, a red flag that you should look for alternatives.

    What will be invoking the process that exports XML? A user pushing a button on a front-end somewhere? A batch process?

    bcp.exe can extract xml columns to disk.

    hi

    i will start searchin on (IMHO) , my idea is im tryin to get the last inserted record out of sql to a folder so there is another another program (MIRTH) will read wat comes to the folder so it send it to a specific system . thats my idea and i mentioned wat i did with bcp command in my previous post i got it workin to export but it failed to recognize the last inserted record so i thought its a bad idea , but i have no clue about the disadvantages of doin this bcp or CLR , anything with security ? and any suggestions ?

    thank you 🙂

  • I would strongly advise against trying to write a file from within a trigger. A more sound approach would be to:

    1. Create a staging table to hold the data you eventually want to export.

    2. Modify your trigger to write data to your new staging table instead of trying to immediately export a file.

    3. Create a SQL Agent Job with a PowerShell step that:

    3.1. Selects data out of the staging table.

    3.2. Writes the xml to a file.

    3.3. Either deletes the processed rows or somehow marks the rows 'processed' so it will not try processing the rows again.

    4. Schedule the SQL Agent Job to run as often as necessary.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • BTW, IMHO stands for In My Humble Opinion

    PS BTW stands for By The Way

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (12/21/2011)


    BTW, IMHO stands for In My Humble Opinion

    PS BTW stands for By The Way

    ^^^haha xD yes i looked on that and it was out of the subject i was like 0.o !!! at least i learned somthing 😛

    yes , your suggestion sounds good , but im gonna be dealing with so many tables from so many databases so wat ur saying to make a staging table for processing , i mean one staging table will deal with all other tables ?

    wat u said is new to me but im gonna start look at it , and see how it goes

    one more thing , wat is the most suitable way to do wat i want from wat i mentioned bcp - CLR - any other

    , thx

  • GSquared (12/21/2011)


    I'm not exactly expert at CLR, but I've bumbled my way through writing a text file using VB.NET's file operations just by digging through Bing searches on the subject. It's not that hard to do. I can't remember how I did it (was 3 years ago), but I remember it not being particularly difficult. Writing an XML file should be easy enough, unless you have oddball requirements.

    Have you thought about simply having SSIS export the file instead? That'll be even easier.

    thank you

    Cadavre (12/21/2011)


    Totally untested, I'm not at my desk any more.

    using Microsoft.SqlServer.Server;

    using System.Data.SqlTypes;

    using System;

    using System.IO;

    namespace XMLWriter

    {

    public class XMLWriter

    {

    [SqlFunction]

    public static SqlString ReplaceMatch(

    SqlString inputXMLString,

    SqlString outputFileDIR)

    {

    try

    {

    // input parameters must not be NULL

    if (!inputXMLString.IsNull &&

    !outputFileDIR.IsNull)

    {

    System.IO.StreamWriter file = new System.IO.StreamWriter(outputFileDIR);

    file.WriteLine(inputXMLString);

    file.Close();

    }

    else

    // if any input paramater is NULL, return NULL

    return SqlString.Null;

    }

    catch

    {

    // on any error, return NULL

    return SqlString.Null;

    }

    return new SqlString();

    }

    };

    }

    will give it a try thx buddy 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply