How to use XML to configure a Store procedure

Question

What I'd like to do is keep some configuration in an external XML file and my stored procedure to open this and use the settings defined in it. Is this possible? I dont want to store the XML in a table.

Answer 1

You can look into this article about Reading and Wrinting files in SQL Server using tsql after that you might want to check out the OPENXML-documentation

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

This might be what you are looking for.

Answer 2

Store it in a table, or as a database extended property, or anything similar, contained in the database. Storing any sort of dependent item, like a configuration XML, outside the database is a bad idea. It will create backup and recovery dependencies, as well as failover problems.

Answer 3

Coolcoder,

For storing our database access code in xml, we use iBatis.Net and it works quite well. This you store your configuration for accessing your database, the configuration of the table to model mappings, and your stored procedures (and parameter mappings) in xml files.

Hope this helps,

Thanks