DLL for XML Data Transfer

[acx]

Dear Group,

I have got some XML code which I need to send from my SQL Server
database to some remote application server via POST method. The
application server will return XML code as well and this returned XML
will be processed on SQL Server afterwards.

Because of the fact that it all can't be done via T-SQL I was advised
on using CLR. This means that I should write some DLL in VB.NET or C#,
place this DLL on SQL Server, register it and write a CLR stored
procedure with the reference to that DLL. Then, when I want to
communicate with the application server, I will execute the CLR SP (I
will pass the proper XML code to it as input parameter), this CLR SP
will call the DLL, the DLL will send the XML to the application server
(via POST method), the application server will execute it and send the
XML answer back to my CLR SP. This XML I will process and save into
tables afterwards. I hope I understand it good...

The problem is that I have no idea how to write that DLL. I have never
programmed in Visual Studio. Can you pls advise me how to make the DLL
which receives XML code, sends it (using .Method="POST") to the
application server, receives the XML answer from that server and
passes it to the calling entity (CLR SP)?


Many thanks for your answers!


MikeX

 

[sloan]

You've got a couple of different ways you can go.

Let me give one.


You have a source1.xml.
You don't control the formatting of source1.xml.

You can write a stored procedure (uspImportMyXml ) to handle source1.xml.
However, if you ever get into the situation where you need to handle a
different xml (source2.xml), you'll have to write another uspImportMyXml2.
I don't like that scenario.

You can write a strong dataset , which you will take your source1.xml, and
turn it into a dataset. You can use the dataset(.GetXml) method and send
that to the uspImportMyXml.

The bonus for you is that you can also return dataset.GetXml() without
pulling it back out of the database.


You also may want to write a WebService, instead of relying on the POST
method.
Your html page ( import.aspx ) can piggyback off the webservice if you'd
like.

Your other issue is performance. How much xml are you talking about?


Some articles:
Xml to Xml (or to DataSet) conversion:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!148.entry


A decent template for a VS2005 project:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry


How to pass Xml into a stored procedure:
http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp
(not an exact match, but you can find the OPENXML usage there)
This again is a way to pass Xml into a stored procedure for processing.
There are some performance issues with OPENXML you might consider. But I've
found using this method for bulk data is better than running the same stored
procedure over and over in a loop for multiple records.
What you find there is a derivative of this MSKB
http://support.microsoft.com/kb/315968


Again, this is ` of a few ways you can go. Look at other ideas also.

But that's how I would approach it.

 

[Egghead]

No idea why u need a DLL, you can write an app to do it. If you must have
the SQL's DTS start the process, your dll need to be an ActiveX dll.
Anyway, just make the function returning the stream.

 

[Nicholas Paldino [.NET/C# MVP]]

Mike,

The first thing you are going to want to read up on is the section of
the MSDN documentation titled "CLR Stored Procedures", located at:

http://msdn2.microsoft.com/en-us/library/ms131094.aspx

You will also want to take a look at the CREATE ASSEMBLY documentation
which you will need in order to register the assembly once you have coded it
up:

http://msdn2.microsoft.com/en-us/library/ms189524.aspx

Take notice of the WITH PERMISSION_SET option, as you will have to set
it to EXTERNAL_ACCESS in order to allow your CLR stored procedure network
access.

If the request is a simple one, then take a look at the WebClient class:

http://msdn2.microsoft.com/en-us/library/system.net.webclient.aspx

The methods you are most likely to use are the UploadFile, UploadString,
or UploadData methods.

If you need more fine-grained control over the request that you are
sending, use the HttpWebRequest class:

http://msdn2.microsoft.com/en-us/library/system.net.httpwebrequest.aspx

Hope this helps.

 

[Nicholas Paldino [.NET/C# MVP]]

That's just a bad idea all around. Using the CLR in SQL Server is much
safer (in comparison to using a COM object), and more efficient than using a
COM object to write an extended stored procedure which will be triggered
from within SQL Server.

 

[Egghead]

You are right, it is not that safe with ActiveX;
I completely miss the CLR in SQL 2k5 part.

--
cheers,
RL

That's just a bad idea all around. Using the CLR in SQL Server is much
safer (in comparison to using a COM object), and more efficient than using
a COM object to write an extended stored procedure which will be triggered
from within SQL Server.