We have a table in our database that stores XML in one of the columns. The XML is always in the exact same format out of a set of 3 different XML formats which is received via web service responses. We need to look up information in this table (and inside of the XML field) very frequently. Is this a poor use of the XML datatype?
我们的数据库中有一个表，它将XML存储在其中一列中。通过web服务响应接收的三种不同的XML格式中的XML格式总是完全相同的。我们需要频繁地在这个表(以及XML字段内部)中查找信息。这是XML数据类型的糟糕使用吗?
My suggestion is to create seperate tables for each different XML structure as we are only talking about 3 with a growth rate of maybe one new table a year.
我的建议是为每个不同的XML结构创建独立的表，因为我们只讨论3个表，增长率可能是每年一个新表。
2 个解决方案
#1
2
I suppose ultimately this is a matter of preference, but here are some reasons I prefer not to store data like that in an XML field:
我认为最终这是一个偏好问题，但以下是我不喜欢在XML字段中存储数据的一些原因:
-
Writing queries against XML in TSQL is slow. Might not be too bad for a small amount of data, but you'll definitely notice it with a decent amount of data.
在TSQL中编写针对XML的查询很慢。对于少量的数据来说，可能还不算太坏，但是您肯定会注意到它包含了大量的数据。
-
Sometimes there is special logic needed to work with an XML blob. If you store the XML directly in SQL, then you find yourself duplicating that logic all over. I've seen this before at a job where the guy that wrote the XML to a field was long gone and everyone was left wondering how exactly to work with it. Sometimes elements were there, sometimes not, etc.
有时，使用XML blob需要特殊的逻辑。如果您将XML直接存储在SQL中，那么您就会发现自己重复了这种逻辑。我以前在一份工作中见过这种情况，编写XML到字段的人早就不在了，每个人都想知道如何使用它。有时元素存在，有时不存在，等等。
-
Similar to (2), in my opinion it breaks the purity of the database. In the same way that a lot of people would advise against storing HTML in a field, I would advise against storing raw XML.
类似于(2)，我认为它打破了数据库的纯粹性。就像许多人反对在字段中存储HTML一样，我也反对存储原始XML。
But despite these three points ... it can work and TSQL definitely supports queries against it.
尽管有这三点……它可以工作，TSQL肯定支持针对它的查询。
#2
0
Are you reading the field more than you are writing it?
你读这个领域比你写的多吗?
You want to do the conversion on whichever step you do least often or the step that doesn't involve the user.
您希望对您最不经常执行的步骤或不涉及用户的步骤进行转换。
#1
2
I suppose ultimately this is a matter of preference, but here are some reasons I prefer not to store data like that in an XML field:
我认为最终这是一个偏好问题，但以下是我不喜欢在XML字段中存储数据的一些原因:
-
Writing queries against XML in TSQL is slow. Might not be too bad for a small amount of data, but you'll definitely notice it with a decent amount of data.
在TSQL中编写针对XML的查询很慢。对于少量的数据来说，可能还不算太坏，但是您肯定会注意到它包含了大量的数据。
-
Sometimes there is special logic needed to work with an XML blob. If you store the XML directly in SQL, then you find yourself duplicating that logic all over. I've seen this before at a job where the guy that wrote the XML to a field was long gone and everyone was left wondering how exactly to work with it. Sometimes elements were there, sometimes not, etc.
有时，使用XML blob需要特殊的逻辑。如果您将XML直接存储在SQL中，那么您就会发现自己重复了这种逻辑。我以前在一份工作中见过这种情况，编写XML到字段的人早就不在了，每个人都想知道如何使用它。有时元素存在，有时不存在，等等。
-
Similar to (2), in my opinion it breaks the purity of the database. In the same way that a lot of people would advise against storing HTML in a field, I would advise against storing raw XML.
类似于(2)，我认为它打破了数据库的纯粹性。就像许多人反对在字段中存储HTML一样，我也反对存储原始XML。
But despite these three points ... it can work and TSQL definitely supports queries against it.
尽管有这三点……它可以工作，TSQL肯定支持针对它的查询。
#2
0
Are you reading the field more than you are writing it?
你读这个领域比你写的多吗?
You want to do the conversion on whichever step you do least often or the step that doesn't involve the user.
您希望对您最不经常执行的步骤或不涉及用户的步骤进行转换。