比較具類型的 XML 與不具類型的 XMLCompare Typed XML to Untyped XML

適用於: 是SQL Server否Azure SQL Database否Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

您可以建立 XML 類型的變數、參數和資料行。You can create variables, parameters, and columns of the xml type. 此外,也可以選擇性地將 XML 結構描述的集合與 XML 類型的變數、參數和資料行建立關聯。You can optionally associate a collection of XML schemas with a variable, parameter, or column of xml type. 在此情況下,此 XML 資料類型的執行個體即稱為「具類型」 。In this case, the xml data type instance is called typed. 非此種情況下的 XML 執行個體則稱為「不具類型」 。Otherwise, the XML instance is called untyped.

格式正確的 XML 和 xml 資料類型Well-formed XML and the xml Data Type

XML 資料類型會實作 ISO 標準 XML 資料類型。The xml data type implements the ISO standard xml data type. 因此,它可以在不具類型的 XML 資料行中儲存格式良好的 XML 1.0 版文件,也可以儲存含有文字節點和任意數量之最上層元素的所謂 XML 內容片段。Therefore, it can store well-formed XML version 1.0 documents and also so-called XML content fragments with text nodes and an arbitrary number of top-level elements in an untyped XML column. 系統會確認資料的格式良好、不需要將資料行繫結到 XML 結構描述,並拒絕在某種程度上格式不良的資料。The system checks that the data is well-formed, does not require the column to be bound to XML schemas, and rejects data that is not well-formed in the extended sense. 對於不具類型的 XML 變數和參數而言,也是如此。This is true also of untyped XML variables and parameters.

XML 結構描述XML Schemas

XML 結構描述提供下列項目:An XML schema provides the following:

  • 驗證的條件約束Validation constraints. :每當指派或修改了具類型的 xml 執行個體,SQL Server 就會驗證該執行個體。Whenever a typed xml instance is assigned to or modified, SQL Server validates the instance.

  • 資料類型資訊Data type information. 結構描述會提供 XML 資料類型執行個體中屬性和元素類型的相關資訊。Schemas provide information about the types of attributes and elements in the xml data type instance. 這些類型資訊針對執行個體中所包含之值所提供的運算語意,要比不具類型的 XML更精確。The type information provides more precise operational semantics to the values contained in the instance than is possible with untyped xml. 例如,十進位的數學運算可以在十進位值上執行,但不能在字串值上執行。For example, decimal arithmetic operations can be performed on a decimal value, but not on a string value. 因此,具類型的 XML 儲存會比不具類型的 XML 精簡很多。Because of this, typed XML storage can be made significantly more compact than untyped XML.

選擇具類型或不具類型的 XMLChoosing Typed or Untyped XML

在下列情況下,請使用不具類型的 XML 資料類型:Use untyped xml data type in the following situations:

  • 您沒有 XML 資料的結構描述。You do not have a schema for your XML data.

  • 您有結構描述,但您不想讓伺服器驗證資料。You have schemas, but you do not want the server to validate the data. 當應用程式在將資料儲存於伺服器之前執行用戶端驗證時,或是根據結構描述暫時儲存無效的 XML 資料時,或是在伺服器上使用不受支援的結構描述元件時,有時會有這種情形。This is sometimes the case when an application performs client-side validation before storing the data at the server, or temporarily stores XML data that is invalid according to the schema, or uses schema components that are not supported at the server.

在下列情況下,請使用具類型的 XML 資料類型:Use typed xml data type in the following situations:

  • 您有 XML 資料的結構描述,而且想要讓伺服器根據該 XML 結構描述來驗證 XML 資料。You have schemas for your XML data and you want the server to validate your XML data according to the XML schemas.

  • 您想要依據類型資訊來利用儲存體及查詢最佳化作業。You want to take advantage of storage and query optimizations based on type information.

  • 您想要在編譯查詢期間更有效地利用類型資訊。You want to take better advantage of type information during compilation of your queries.

具類型的 XML 資料行、參數及變數可儲存 XML 文件或內容。Typed XML columns, parameters, and variables can store XML documents or content. 但是您必須在宣告時,用旗標來指定您是要儲存文件還是內容。However, you have to specify with a flag whether you are storing a document or content at the time of declaration. 此外,您也必須提供 XML 結構描述的集合。Additionally, you have to provide the collection of XML schemas. 如果每個 XML 執行個體都只有一個最上層元素,請指定 DOCUMENT。Specify DOCUMENT if each XML instance has exactly one top-level element. 否則,請使用 CONTENT。Otherwise, use CONTENT. 查詢編譯器會在查詢編譯期間,於類型檢查中使用 DOCUMENT 旗標,以推斷單一最上層元素。The query compiler uses the DOCUMENT flag in type checks during query compilation to infer singleton top-level elements.

建立具類型的 XMLCreating Typed XML

您必須先使用 CREATE XML SCHEMA COLLECTION (Transact-SQL) 註冊 XML 結構描述集合之後,才能夠建立具類型的 XML 變數、參數或資料行。Before you can create typed xml variables, parameters, or columns, you must first register the XML schema collection by using CREATE XML SCHEMA COLLECTION (Transact-SQL). 然後,您可以將 XML 結構描述集合與 XML 資料類型的變數、參數或資料行產生關聯。You can then associate the XML schema collection with variables, parameters, or columns of the xml data type.

在下列範例中,會使用兩段式命名慣例來指定 XML 結構描述集合名稱。In the following examples, a two-part naming convention is used for specifying the XML schema collection name. 第一個部分是結構描述名稱,而第二個部分是 XML 結構描述集合名稱。The first part is the schema name, and the second part is the XML schema collection name.

範例:將結構描述集合與 xml 類型變數產生關聯Example: Associating a Schema Collection with an xml Type Variable

下列範例會建立一個 xml 類型變數,並將結構描述集合與此變數產生關聯。The following example creates an xml type variable and associates a schema collection with it. 範例中指定的結構描述集合已經匯入 AdventureWorks 資料庫。The schema collection specified in the example is already imported in the AdventureWorks database.

DECLARE @x xml (Production.ProductDescriptionSchemaCollection);   

範例:指定 xml 類型資料行的結構描述Example: Specifying a Schema for an xml Type Column

下列範例會建立具有 XML 類型資料行的資料表,並為此資料行指定結構描述:The following example creates a table with an xml type column and specifies a schema for the column:

CREATE TABLE T1(  
 Col1 int,   
 Col2 xml (Production.ProductDescriptionSchemaCollection)) ;  

範例:將 xml 類型的參數傳遞給預存程序Example: Passing an xml Type Parameter to a Stored Procedure

下列範例會將 XML 類型的參數傳遞給預存程序,並為此變數指定結構描述:The following example passes an xml type parameter to a stored procedure and specifies a schema for the variable:

CREATE PROCEDURE SampleProc   
  @ProdDescription xml (Production.ProductDescriptionSchemaCollection)   
AS   
...  

請注意下列有關 XML 結構描述集合的項目:Note the following about the XML schema collection:

  • XML 結構描述集合只適用於使用 建立 XML 結構描述集合來註冊它的資料庫。An XML schema collection is available only in the database in which it was registered by using Creating an XML Schema Collection.

  • 如果您將字串轉換成具類型的 XML 資料類型,則剖析作業也會根據所指定之集合中的 XML 結構描述命名空間來執行驗證和設定類型。If you cast from a string to a typed xml data type, the parsing also performs validation and typing, based on the XML schema namespaces in the collection specified.

  • 您可以將具類型的 XML 資料類型轉換為不具類型的 XML 資料類型,反之亦然。You can cast from a typed xml data type to an untyped xml data type, and vice versa.

如需在 SQL Server 中產生 XML 之其他方法的詳細資訊,請參閱 建立 XML 資料的執行個體For more information about other ways to generate XML in SQL Server, see Create Instances of XML Data. 在產生 XML 之後,可將它指派給 XML 資料類型的變數,或將它儲存在 XML 類型資料行中,以進行其他處理。After XML is generated, it can be assigned either to an xml data type variable or stored in xml type columns for additional processing.

在資料類型階層中, XML 資料類型會出現在 sql_variant 和使用者定義類型的下面,但會出現在任何內建類型的上面。In the data type hierarchy, the xml data type appears below sql_variant and user-defined types, but above any of the built-in types.

範例:指定 Facet 來約束 xml 類型的資料行Example: Specifying Facets to Constrain a Typed xml Column

對於具類型的 XML 資料行而言,您可以約束資料行,讓儲存在其中的每個執行個體只能有單一的最上層元素。For typed xml columns, you can constrain the column to allow only single, top-level elements for each instance stored in it. 作法是,在建立資料表時指定選擇性的 DOCUMENT Facet,如下列範例所示:You do this by specifying the optional DOCUMENT facet when a table is created, as shown in the following example:

CREATE TABLE T(Col1 xml   
   (DOCUMENT Production.ProductDescriptionSchemaCollection));  
GO  
DROP TABLE T;  
GO  

依預設,儲存在具類型的 XML 資料行中的執行個體會儲存為 XML 內容,而非 XML 文件。By default, instances stored in the typed xml column are stored as XML content and not as XML documents. 這樣做可允許有下列項目:This allows for the following:

  • 零個或者許多最上層元素Zero or many top-level elements

  • 最上層元素內的文字節點Text nodes in top-level elements

您也可以藉由加入 CONTENT Facet,明確指定此行為,如下列範例所示:You can also explicitly specify this behavior by adding CONTENT facet, as shown in the following example:

CREATE TABLE T(Col1 xml(CONTENT Production.ProductDescriptionSchemaCollection));  
GO -- Default  

請注意,您可以在任何定義 XML 類型 (具類型的 XML) 的地方,指定選擇性的 DOCUMENT/CONTENT Facet。Note that you can specify the optional DOCUMENT/CONTENT facets anywhere you define xml type (typed xml). 例如,當您建立具類型的 XML 變數時,可以加入 DOCUMENT/CONTENT Facet,如下所示:For example, when you create a typed xml variable, you can add the DOCUMENT/CONTENT facet, as shown in the following:

declare @x xml (DOCUMENT Production.ProductDescriptionSchemaCollection);  

文件類型定義 (DTD)Document Type Definition (DTD)

XML 資料類型資料行、變數及參數可以用 XML 結構描述來設定類型,但不能用 DTD 來設定。The xml data type columns, variables, and parameters can be typed by using XML schema, but not by using DTD. 但是,內嵌 DTD 可用於不具類型和具類型的 XML 來提供預設值,並將實體參考取代為其展開的形式。However, inline DTD can be used for both untyped and typed XML to supply default values and to replace entity references with their expanded form.

您可以用協力廠商工具將 DTD 轉換成 XML 結構描述文件,並將 XML 結構描述載入資料庫中。You can convert DTDs to XML schema documents by using third-party tools, and load the XML schemas into the database.

從 SQL Server 2005 升級具類型的 XMLUpgrading Typed XML from SQL Server 2005

SQL Server 2008SQL Server 2008 對於 XML 結構描述支援做了幾項擴充,其中包括支援 Lax 驗證,改進 xs:datexs:timexs:dateTime 執行個體資料的處理,並加入了清單和聯集類型的支援。made several extensions to the XML Schema support, including support for lax validation, improved handling of xs:date, xs:time and xs:dateTime instance data, and added support for list and union types. 在大多數情況下,這些變更並不會影響升級的使用體驗。In most cases the changes do not affect the upgrade experience. 但是,如果您在 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 中使用允許 xs:datexs:timexs:dateTime 類型 (或任何子類型) 值的 XML 結構描述集合,則當您將 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 資料庫附加到更新版的 SQL ServerSQL Server時,會進行下列升級步驟:However if you used an XML Schema collection in SQL Server 2005 (9.x)SQL Server 2005 (9.x) that allowed values of type xs:date, xs:time, or xs:dateTime (or any subtype) then the following upgrade steps occur when you attach your SQL Server 2005 (9.x)SQL Server 2005 (9.x) database to a later version of SQL ServerSQL Server:

  1. 對於每一個 XML 資料行而言,如果它在 XML 結構描述集合中具類型 (該集合包含的元素或屬性具有 xs:anyTypexs:anySimpleTypexs:date 類型或它的任何子類型、 xs:time 或它的任何子類型或是 xs:dateTime 或它的任何子類型),或是為包含這些類型之任何一項的聯集或清單類型時,會發生下列情況:For every XML column, that is typed with an XML Schema Collection that contains elements or attributes that are typed as either xs:anyType, xs:anySimpleType, xs:date or any of its subtypes, xs:time or any subtype thereof, or xs:dateTime or any of its subtypes, or are union or list types containing any of these types the following occurs:

    1. 資料行上的所有 XML 索引都將會停用。All XML indices on the column will be disabled.

    2. 所有的 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 值都將會繼續以 Z 時區來表示,因為這些值已經正規化成 Z 時區。All SQL Server 2005 (9.x)SQL Server 2005 (9.x) values will continue to be represented in the Z timezone, because they have been normalized to the Z timezone.

    3. 在下列情況下,任何小於 1 年 1 月 1 日的 xs:datexs:dateTime 值都會導致發生執行階段錯誤:當重建索引時,或是針對包含該值的 XML 資料類型執行 XQuery 或 XML-DML 陳述式時。Any xs:date or xs:dateTime values that are smaller than January 1st of year 1 will lead to a runtime error when the index gets rebuild or an XQuery or XML-DML statements gets executed against the XML data type containing that value.

  2. xs:datexs:dateTime Facet 中的任何負數年份或是 XML 結構描述集合中的預設值,將會自動更新為基底 xs:datexs:dateTime 類型所允許的最小值 (例如 xs:dateTime的 0001-01-01T00:00:00.0000000Z)。Any negative years in xs:date or xs:dateTime facets or default values in an XML Schema collection will automatically be updated to the smallest value allowed by the base xs:date or xs:dateTime type (e.g., 0001-01-01T00:00:00.0000000Z for xs:dateTime).

請注意,您仍然可以使用簡單 SQL SELECT 陳述式來擷取整個 XML 資料類型,即使它包含負數年份。Note that you can still use a simple SQL select statement to retrieve the whole XML data type, even if it contains negative years. 建議您使用新支援範圍中的年份來取代負數年份,或是將元素或屬性的類型變更為 xs:stringIt is recommended that you replace negative years with a year within the newly supported range or change the type of the element or attribute to xs:string.

另請參閱See Also

建立 XML 資料的執行個體 Create Instances of XML Data
xml 資料類型方法 xml Data Type Methods
XML 資料修改語言 (XML DML) XML Data Modification Language (XML DML)
XML 資料 (SQL Server)XML Data (SQL Server)