CODEWITHSUNDEEP
sql-serverxmlwcfweb-services
ManOnAMission
ManOnAMission

Reputation: 1043

How to store XML result of WebService into SQL Server database?

We have got a .Net Client that calls a Webservice. We want to store the result in a SQL Server database.

I think we have two options here how to store the data, and I am a bit undecided as I can't see the pros and cons clearly: One would be to map the results into database fields. That would require us to have database fields corresponding to each possible result type, e.g. for each "normal" result type as well as those for faults.

On the other hand, we could store the resulting XML and query that via the SQL Server built in XML functions.

Personally, I am comfortable with dealing with both SQL and XML, so both look fine to me.

Are there any big pros and cons and what would I need to consider in terms of database design when trying to store the resulting XML for quite a few different possible Webservice operations? I was thinking about a result table for each operation that we call with different entries for the different possible outcomes / types and then store the XML in the right field, e.g. a fault in the fault field, a "normal" return type in the appropriate field etc.

Upvotes: 0

Views: 1264

Answers (1)

Andomar
Andomar

Reputation: 238256

We use a combination of both. XML for reference and detailed data, and text columns for fields you might search on. Searchable columns include order number, customer reference, ticket number. We just add them when we need them since you can extract them from the XML column.

I wouldn't recommend just the XML. If you store 10.000 messages a day, a query like:

select * from XmlLogging with (nolock) where Response like '%Order12%'

can become slow and interfere with other queries. You also can't display the logging in a GUI because retrieval is too slow.

I wouldn't recommend just the text columns either. If the XML format changes, you'd get an empty column. That's hard to troubleshoot without the XML message. In addition, if you need to "replay" the message stream, that's a lot easier with the XML messages. Few requirements demand replay, but it's really helpful when repairing the fallout of production problems.

Upvotes: 4

Related Questions