Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CLR procedure to export xml file ? Expand / Collapse
Author
Message
Posted Tuesday, December 20, 2011 7:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 6:17 PM
Points: 5, Visits: 9
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 :)
Post #1224833
Posted Wednesday, December 21, 2011 9:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
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
Post #1225243
Posted Wednesday, December 21, 2011 9:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1225251
Posted Wednesday, December 21, 2011 9:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
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();
}
};
}




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1225299
Posted Wednesday, December 21, 2011 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
blah...

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1225302
Posted Wednesday, December 21, 2011 3:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 6:17 PM
Points: 5, Visits: 9
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 :)
Post #1225471
Posted Wednesday, December 21, 2011 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 6:17 PM
Points: 5, Visits: 9
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 :)
Post #1225477
Posted Wednesday, December 21, 2011 4:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
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
Post #1225485
Posted Wednesday, December 21, 2011 4:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
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
Post #1225487
Posted Wednesday, December 21, 2011 5:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 6:17 PM
Points: 5, Visits: 9
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 :P

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
Post #1225503
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse