transform xml messages into SQL Sever

[Prefers Golfing]

We receive xml messages from Corporatevia WebSphere , create a dataset from
each message, transform that dataset with C# code into another DataSet to
perform updates to our SQL Server tables. My question is, is there another
way to do this? This approach is solid but I was wandering what others were
doing to transform xml messages into SQL Server.

We are expanding this functionality considerably over the next three
quarters and are looking at reengineering for ease of maintenance and speed.

Thank you for your time

 

[Cowboy \(Gregory A. Beamer\)]

Sure.

One way is to feed the XML into SQL as XML (sproc input, for example) and
consume it as XML. It is a bit slower than other ways of getting data in,
but it is fine for small numbers of records. I am not sure which is faster,
this or DataSets, but I would assume there are a bit fewer moving parts in
the XML in SQL. I have no firm proof. Both are "slow" options.

A faster option (ie, more performant) might be BizTalk, as you can use
Orchestration to do both the transform and the load. It is a more expensive
option, but for message based systems, it is one of the quickest ways to get
a new client on board.

If they are dumping the XML, as files, you could do an XML bulk load and
then transform in SQL Server. This is not a great option if your server is
already overloaded, of course.



--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************