SQL Server中的XML数据类型?

I have some unstructured data that describe setting for each devices. For e.g. for Device 1:

我有一些非结构化数据，描述每个设备的设置。例如，对于装置1:

<ChartSetting xmlns="ChartSetting1">
  <ChartSizeX>132.6</ChartSizeX>
  <ChartSizeY>132.6</ChartSizeY>
  <ChartType>Native</ChartType>
  <BarSizeX>90</BarSizeX>
  <BarSizeY>6</BarSizeY>
  <DistToBar>34.8</DistToBar>
  <DistToFirstLineY>17.5</DistToFirstLineY>
  <MarkerDistance>120</MarkerDistance>
  <DistToFirstField>18.5</DistToFirstField>
  <PatchSizeX>7.5</PatchSizeX>
  <PatchSizeY>9</PatchSizeY>
</ChartSetting>

However for Device 2 setting is different

但是对于设备2的设置是不同的

<ChartSetting xmlns="ChartSetting2">
  <PatchGap>1</PatchGap>
  <PatchSize>5</PatchSize> 
</ChartSetting>

xml data is not used for query purpose, it will be passed to the .net application that eventually send it to the devicedriver (through C++ code) We only have four types device. So possibility of different settings is limited.

xml数据不用于查询目的，它将被传递给.net应用程序，最终将它发送到devicedriver(通过c++代码)，我们只有4种类型的设备。所以不同设置的可能性是有限的。

Can this kind of data be stored as typed XML that adhere to some schema? Storing as varchar will be nightmare if there are invalid settings stored.

这种数据可以存储为符合某种模式的类型化XML吗?如果存储的设置无效，以varchar格式存储将是一场噩梦。

I know xml schema collection can have multiple schema but can it confirms to only one schema.

我知道xml模式集合可以有多个模式，但它可以只确定一个模式。
Is there an easy way to create a schema?

是否有一种简单的方法来创建模式?
Should I be using untyped XML instead?

我应该使用非类型化的XML吗?

4 个解决方案

#1

1.I think it's possible, if your schema collection contains 4 different schemas, the related column can contain xml that satisfies one of the schemas in collection.
2.I believe the easiest way is to let sql server create a schema for you (first create tables ChartSetting1,ChartSetting2 with desired columns - it's not necessarily, you can create SELECT that returns all columns, but using tables is easier), then

1。我认为，如果您的模式集合包含4个不同的模式，那么相关列可以包含满足集合中的一个模式的xml。2。我认为最简单的方法是让sql server为您创建一个模式(首先创建表ChartSetting1,ChartSetting2以及所需的列——不一定，您可以创建返回所有列的SELECT，但是使用表更容易)

DECLARE @mySchema NVARCHAR(MAX);  
SET @mySchema = N'';  
SET @mySchema =@mySchema  +   
(
  SELECT * FROM ChartSetting1 
  FOR XML AUTO, ELEMENTS, XMLSCHEMA('ChartSetting1')
);

SET @mySchema =@mySchema  +   
(
  SELECT * FROM ChartSetting2 
  FOR XML AUTO, ELEMENTS, XMLSCHEMA('ChartSetting2')
);

CREATE XML SCHEMA COLLECTION [name] AS @mySchema;

-- we don't need these tables , drop them  
DROP TABLE ChartSetting1,ChartSetting2

3.It depends. I'd say if you need constrain the XML data beyond schema validation, then it makes sense to use untyped XML. If schema validation covers all aspects of data validation, why not use it?

3所示。视情况而定。如果需要在模式验证之外约束XML数据，那么使用非类型化的XML是有意义的。如果模式验证涵盖了数据验证的所有方面，为什么不使用它呢?

#2

You could create a DTD for the four different device types and validate your XML fragments with those DTDs, but I'd suggest you do that processing OUTSIDE of SQL Server.

您可以为这四种不同的设备类型创建DTD，并使用DTD来验证您的XML片段，但是我建议您在SQL Server之外进行处理。

If you are not going to query the XML, there isn't any reason to store it as the XML data type.

如果您不打算查询XML，则没有任何理由将其存储为XML数据类型。

Answers to your questions:

你的问题的答案:

Typed XML: you'll have to use a specific type per column (see http://msdn.microsoft.com/en-us/library/ms184277.aspx)
类型化XML:您必须为每个列使用特定的类型(参见http://msdn.microsoft.com/en-us/library/ms184277.aspx)
Create a schema - check out (http://msdn.microsoft.com/en-us/library/ms176009.aspx) and try something like XMLSpy if it gets too complex. Your snippets looked small so I should think you can manage it with Notepad/++
创建一个模式-签出(http://msdn.microsoft.com/en-us/library/ms176009.aspx)，如果它太复杂，可以尝试XMLSpy之类的东西。您的代码片段看起来很小，所以我认为您可以用Notepad/++来管理它。
I'd use untyped XML and validate it when it gets stored and/or retrieved
我将使用非类型化的XML，并在存储和/或检索它时验证它

#3

I think it is impossible create schema that conforms both XML.
我认为创建符合XML的模式是不可能的。
Many XML editors can create XSD from XML, e.g.: Visual Studio
许多XML编辑器可以从XML创建XSD，例如:Visual Studio
Typed XML vs. Untyped XML
类型化XML与非类型化XML

#4

-2

XML shouldent really be stored in a databse as XML. XML is more of a transport medium than data. As you say its big and hard to query. You should store your data as data (varchar int ect) then use PATH MODE to return it back to you in the format you want

XML应该作为XML存储在数据库中。XML与其说是数据，不如说是一种传输媒介。就像你说的，它很大，很难查询。您应该将数据存储为数据(varchar int ect)，然后使用PATH模式以您想要的格式返回给您。

#1

DECLARE @mySchema NVARCHAR(MAX);  
SET @mySchema = N'';  
SET @mySchema =@mySchema  +   
(
  SELECT * FROM ChartSetting1 
  FOR XML AUTO, ELEMENTS, XMLSCHEMA('ChartSetting1')
);

SET @mySchema =@mySchema  +   
(
  SELECT * FROM ChartSetting2 
  FOR XML AUTO, ELEMENTS, XMLSCHEMA('ChartSetting2')
);

CREATE XML SCHEMA COLLECTION [name] AS @mySchema;

-- we don't need these tables , drop them  
DROP TABLE ChartSetting1,ChartSetting2