Can I create a dynamic view of xml data in Sql Server 2005?

Question

We have a situation where we need to store form data in our sql server but each new job we setup will have different fields with different field names and lengths. An example

Job 1:
    Field 1: first_name - varchar(20)
    Field 2: last_name - varchar(30)

Job 2:
    Field 1: first_name - varchar(15)
    Field 2: middle_initial - varchar(1)
    Field 3: last_name - varchar(30)

Initially we were setting up separate tables to store this data which conformed exactly to the form in question. But it lead to a maintenance nightmare as there were so many tables, procs, dts, ssis packages to be changing each time in order to accomodate the dynamic nature of this data.

We came up with a different solution to store all of the data in xml fields thus solving most of the problem. It now exists similar to this.

<Record>
  <first_name>value</first_name>
  <last_name>value</last_name>
</Record>

Then we would create views to pull this data out of the table

SELECT
,  IsNull(data.value('(/Record/first_name)[1]', 'varchar(20)'),'') as first_name
,  IsNull(data.value('(/Record/last_name)[1]', 'varchar(30)'),'') as last_name
FROM FormTable

Now this is much better than we had before but it also means that we still need to create the custom view each time. I'd much rather maintain some type of table that lists the fields and will build that query for me.

Field Name | Field Type | Field Length
first_name | varchar    | 20
last_name  | varchar    | 30

I'm pretty sure I cannot create a dynamic view. One option that could work is a table valued function. But is there something that I am overlooking here? Would there be better options to be able to dynamically store the data in this way (without moving away from SQL SERVER since i know other databases such as CouchDB will do this natively.)

Answer 1

I'm pretty sure a table valued function would not work, since you can't use dynamic sql or temp tables (which you would almost certainly need to use to do this).

A stored procedure would be the obvious choice - it can do everything you need to do, but the problem is of course that you can't SELECT from a stored procedure.

I was looking at this page that discusses a bunch of options for doing things like this. One of the options he mentions is using OPENQUERY, which seems like it would be very easy, but there could be performance problems:

SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC sp_getformdata')

Anyways, you might want to check out that link to get some additional ideas.