我可以在 Sql Server 2005 中创建 xml 数据的动态视图吗？
我们有一种情况，我们需要在 SQL Server 中存储表单数据，但我们设置的每个新作业都会有不同的字段，这些字段具有不同的字段名称和长度。 示例
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)
最初，我们设置单独的表来存储与所讨论的表单完全一致的数据。 但这会导致维护噩梦，因为每次都要更改许多表、procs、dts、ssis 包，以便适应这些数据的动态特性。
我们提出了一个不同的解决方案，将所有数据存储在 xml 字段中，从而解决了大部分问题。 现在也有类似的情况存在。
<Record>
<first_name>value</first_name>
<last_name>value</last_name>
</Record>
然后我们将创建视图以从表中提取这些数据
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
现在这比以前好得多，但这也意味着我们仍然需要每次创建自定义视图。 我更愿意维护某种类型的表来列出字段并为我构建该查询。
Field Name | Field Type | Field Length
first_name | varchar | 20
last_name | varchar | 30
我很确定我无法创建动态视图。 一种可行的选择是表值函数。 但我在这里忽略了什么吗？ 是否有更好的选择能够以这种方式动态存储数据（无需离开 SQL SERVER，因为我知道其他数据库（例如 CouchDB）会本机执行此操作。）
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.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我很确定表值函数不起作用，因为您不能使用动态 sql 或临时表（您几乎肯定需要使用它们来执行此操作）。
存储过程将是显而易见的选择 - 它可以完成您需要做的所有事情，但问题当然是您无法从存储过程中进行 SELECT。
我正在查看 此页面，其中讨论了执行此类操作的一系列选项。 他提到的选项之一是使用 OPENQUERY，这看起来很简单，但可能会出现性能问题：
无论如何，您可能想查看该链接以获得一些额外的想法。
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:
Anyways, you might want to check out that link to get some additional ideas.