XML 資料類型和資料行 (SQL Server)XML Data Type and Columns (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題討論 xml SQL ServerSQL Server資料類型的優勢和限制,並幫助您選擇儲存 XML 資料的方式。This topic discusses the advantages and the limitations of the xml data type in SQL ServerSQL Server, and helps you to choose how to store XML data.

關聯式或 XML 資料模型Relational or XML Data Model

如果您的資料使用已知的結構描述來高度結構化,則關聯式模型對資料儲存來說可能是最好的方式。If your data is highly structured with known schema, the relational model is likely to work best for data storage. SQL ServerSQL Server 提供必要的功能以及您可能需要的工具。provides the required functionality and tools you may need. 另一方面,如果結構是半結構化或是無結構,或是情況不明,您就必須考慮將這類資料模型化。On the other hand, if the structure is semi-structured or unstructured, or unknown, you have to give consideration to modeling such data.

如果您想要一個與平台沒有關聯的模型,以使用結構化及語意化的標記來確保資料的可攜性,則 XML 會是個很好的選擇。XML is a good choice if you want a platform-independent model in order to ensure portability of the data by using structural and semantic markup. 此外,若符合下列的部份屬性,XML 也會是很適當的選項:Additionally, it is an appropriate option if some of the following properties are satisfied:

  • 您的資料很少,或是您不知道資料的結構,或是資料的結構未來可能會有重大變更。Your data is sparse or you do not know the structure of the data, or the structure of your data may change significantly in the future.

  • 您的資料代表內含項目階層,而不是實體之間的參考,而且可能是遞迴式的。Your data represents containment hierarchy, instead of references among entities, and may be recursive.

  • 順序是資料固有的。Order is inherent in your data.

  • 您想要依據資料結構來查詢資料,或是更新部份資料。You want to query into the data or update parts of it, based on its structure.

若沒有符合上述任一情況,則您應使用關聯式資料模型。If none of these conditions is met, you should use the relational data model. 例如,若資料為 XML 格式,但您的應用程式只是使用資料庫來儲存及擷取資料,您就只需要 [n]varchar(max) 資料行。For example, if your data is in XML format but your application just uses the database to store and retrieve the data, an [n]varchar(max) column is all you require. 將資料儲存在 XML 資料行中還有其他好處,Storing the data in an XML column has additional benefits. 其中包括可由引擎來判斷資料結構是否良好以及資料是否有效,也包括了細部查詢及 XML 資料更新的支援。This includes having the engine determine that the data is well formed or valid, and also includes support for fine-grained query and updates into the XML data.

將 XML 資料儲存在 SQL Server 中的理由Reasons for Storing XML Data in SQL Server

以下是一些在 SQL ServerSQL Server 中使用原生 XML 功能,而不在檔案系統中管理 XML 資料的理由:Following are some of the reasons to use native XML features in SQL ServerSQL Server instead of managing your XML data in the file system:

  • 您想要以有效率及交易性的方式來共用、查詢及修改 XML 資料。You want to share, query, and modify your XML data in an efficient and transacted way. 細項資料存取權對您的應用程式很重要。Fine-grained data access is important to your application. 例如,您想要在 XML 文件中擷取某幾段,或是您想要插入新的區段,而不要置換整份文件。For example, you may want to extract some of the sections within an XML document, or you may want to insert a new section without replacing your whole document.

  • 您有關聯式資料及 XML 資料,而且您希望應用程式中的關聯式及 XML 資料之間具有互通性。You have relational data and XML data and you want interoperability between both relational and XML data within your application.

  • 您需要語言支援,以進行跨網域應用程式的查詢及資料修改。You need language support for query and data modification for cross-domain applications.

  • 您希望伺服器能保證資料的結構良好,並依據 XML 結構描述來選擇驗證您的資料。You want the server to guarantee that the data is well formed and also optionally validate your data according to XML schemas.

  • 您想要檢索 XML 資料,以求查詢處理的效率及良好的可調適性,並使用第一級的查詢最佳化工具。You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer.

  • 您想要有 XML 資料的 SOAP、ADO.NET 及 OLE DB 存取權。You want SOAP, ADO.NET, and OLE DB access to XML data.

  • 您想要利用資料庫伺服器的管理功能來管理您的 XML 資料。You want to use administrative functionality of the database server for managing your XML data. 例如,您想要備份、復原及複寫資料。For example, this would be backup, recovery, and replication.

如果沒有符合上述任一情況,可能比較適合將您的資料儲存成非 XML 的大型物件類型,例如 [n]varchar(max)varbinary(max)If none of these conditions is satisfied, it may be better to store your data as a non-XML, large object type, such as [n]varchar(max) or varbinary(max).

XML 儲存選項XML Storage Options

SQL ServerSQL Server 中的 XML 儲存選項如下:The storage options for XML in SQL ServerSQL Server include the following:

  • 原生儲存為 xml 資料類型Native storage as xml data type

    以內部表示法來儲存資料,以保存資料的 XML 內容。The data is stored in an internal representation that preserves the XML content of the data. 這個內部表示法包括有關內含項目階層、文件順序,以及元素和屬性值的資訊。This internal representation includes information about the containment hierarchy, document order, and element and attribute values. 特別是會保存 XML 資料的 InfoSet 內容。Specifically, the InfoSet content of the XML data is preserved. 如需 InfoSet 的詳細資訊,請前往 http://www.w3.org/TR/xml-infosetFor more information about InfoSet, visit http://www.w3.org/TR/xml-infoset. InfoSet 內容可能會與文字版 XML 不同,因為沒有保留下列資訊:不重要的空格、屬性的順序、命名空間前置詞及 XML 宣告。The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.

    針對具類型的 xml 資料類型 (與 XML 結構描述繫結的 xml 資料類型),後置結構描述驗證 InfoSet (PSVI) 會將類型資訊加入 InfoSet 中,而且會以內部表示法來編碼。For typed xml data type, an xml data type bound to XML schemas, the post-schema validation InfoSet (PSVI) adds type information to the InfoSet and is encoded in the internal representation. 這樣可以大幅增加剖析的速度。This improves parsing speed significantly. 如需詳細資訊,請參閱 http://www.w3.org/TR/xmlschema-1http://www.w3.org/TR/xmlschema-2 的「W3C XML 結構描述」規格。For more information, see the W3C XML Schema specifications at http://www.w3.org/TR/xmlschema-1 and http://www.w3.org/TR/xmlschema-2.

  • XML 與關聯式儲存之間的對應Mapping between XML and relational storage

    藉由使用註解式結構描述 (AXSD),XML 會被分解成一或多個資料表中的資料行。By using an annotated schema (AXSD), the XML is decomposed into columns in one or more tables. 如此可以讓資料的精確度保持在關聯式層級。This preserves fidelity of the data at the relational level. 這樣一來,雖然會忽略元素之間的順序,但會保留階層結構。As a result, the hierarchical structure is preserved although order among elements is ignored. 結構描述不能遞迴。The schema cannot be recursive.

  • 大型的物件儲存體 [n]varchar(max)varbinary(max)Large object storage, [n]varchar(max) and varbinary(max)

    會儲存相同的資料副本。An identical copy of the data is stored. 針對特殊目的應用程式 (如:法律文件),這是很有用的。This is useful for special-purpose applications such as legal documents. 大部份的應用程式都不需要完全相同的副本,且可以滿足於 XML 內容 (InfoSet 精確度)。Most applications do not require an exact copy and are satisfied with the XML content (InfoSet fidelity).

一般而言,您可能必須組合使用這些方法。Generally, you may have to use a combination of these approaches. 例如,您想要將 XML 資料儲存在 xml 資料類型資料行中,並將其屬性升級為關聯式資料行。For example, you may want to store your XML data in an xml data type column and promote properties from it into relational columns. 或者,您想要使用對應技術來將非遞迴的部分儲存在非 XML 資料行中,並只將遞迴的部分儲存在 xml 資料類型資料行中。Or, you may want to use mapping technology to store nonrecursive parts in non-XML columns and only the recursive parts in xml data type columns.

XML 技術的選項Choice of XML Technology

XML 技術的選項有原生 XML 和 XML 檢視,通常需視下列因素而定:The choice of XML technology, native XML versus XML view, generally depends upon the following factors:

  • 儲存選項Storage options

    您的 XML 資料可能比較適合大型物件儲存體 (例如,產品手冊),或是比較適合儲存在關聯式資料行中 (例如,轉換成 XML 的線性項目)。Your XML data may be more appropriate for large object storage (for example, a product manual), or more amenable to storage in relational columns (for example, a line item converted to XML). 每個儲存選項保留的文件精確度各不相同。Each storage option preserves document fidelity to a different extent.

  • 查詢功能Query capabilities

    根據查詢的本質,以及您查詢 XML 資料的程度,您可能會發現其中一個儲存選項比另一個更合適。You may find one storage option more appropriate than another, based on the nature of your queries and on the extent to which you query your XML data. 在這兩種儲存選項中,支援 XML 資料細項查詢 (例如:XML 節點的述詞評估) 的程度各不相同。Fine-grained query of your XML data, for example, predicate evaluation on XML nodes, is supported to varying degrees in the two storage options.

  • 檢索 XML 資料Indexing XML data

    您可能會想要檢索 XML 資料,以加速 XML 查詢的效能。You may want to index the XML data to speed up XML query performance. 索引選項會依儲存選項而有所不同;您必須做出適當的選擇,以使您的工作負載最佳化。Indexing options vary with the storage options; you have to make the appropriate choice to optimize your workload.

  • 資料修改功能Data modification capabilities

    有些工作負載需要修改 XML 資料的細項。Some workloads involve fine-grained modification of XML data. 例如,可能需要在文件中加入新的區段,但在其他工作負載中 (例如:Web 內容) 則不需要。For example, this can include adding a new section within a document, while other workloads, such as Web content, do not. 對您的應用程式而言,資料修改的語言支援可能是很重要的。Data modification language support may be important for your application.

  • 結構描述支援Schema support

    您用來描述 XML 資料的結構描述可能是也可能不是 XML 結構描述文件。Your XML data may be described by a schema that may or may not be an XML schema document. 與結構描述繫結之 XML 的支援取決於 XML 技術。The support for schema-bound XML depends upon the XML technology.

不同的選擇也會有不同的效能特質。Different choices also have different performance characteristics.

原生 XML 儲存Native XML Storage

您可以將 XML 資料儲存在伺服器的 xml 資料類型資料行中。You can store your XML data in an xml data type column at the server. 若符合下列情況,則適用此選項:This is an appropriate choice if the following applies:

  • 您想要直接將 XML 資料儲存在伺服器,同時也要保持文件順序和文件結構。You want a straightforward way to store your XML data at the server and, at the same time, preserve document order and document structure.

  • 您可能有也可能沒有 XML 資料的結構描述。You may or may not have a schema for your XML data.

  • 您想要查詢及修改 XML 資料。You want to query and modify your XML data.

  • 您想要檢索 XML 資料,以加速查詢處理。You want to index the XML data for faster query processing.

  • 您的應用程式需要系統目錄檢視來管理 XML 資料及 XML 結構描述。Your application needs system catalog views to administer your XML data and XML schemas.

當您的 XML 文件有一個結構範圍,或是您的 XML 文件符合相異或複雜的結構描述,而這些結構描述難以對應到關聯式結構時,原生 XML 儲存是很有用的。Native XML storage is useful when you have XML documents that have a range of structures, or you have XML documents that conform to different or complex schemas that are too hard to map to relational structures.

範例使用 xml 資料類型將 XML 資料模型化Example: Modeling XML Data Using the xml Data Type

假設有一個 XML 格式的產品手冊,其中每個主題各成一章,每一章中有好幾節。Consider a product manual in XML format that is made up of a separate chapter for each topic and that has multiple sections within each chapter. 每一節中還可包含小節。A section can contain subsections. 因此,<section> 就是遞迴項目。As a result, <section> is a recursive element. 產品手冊包含大量的混合內容、圖表和技術資料;資料是半結構化的。Product manuals contain a large amount of mixed content, diagrams, and technical material; the data is semi-structured. 使用者可能會想要在內容中搜尋感興趣的主題,例如,在有關「檢索」的那一章中搜尋有關「叢集索引」的那一節,並查詢技術數量。Users may want to perform a contextual search for topics of interest such as searching for the section on "clustered index" within the chapter on "indexing", and query technical quantities.

您的 XML 文件所適合的儲存模式就是 xml 資料類型資料行。An appropriate storage model for your XML documents is an xml data type column. 這樣可以保持 XML 資料的 InfoSet 內容。This preserves the InfoSet content of your XML data. 檢索 XML 資料行對於查詢效能是很有益的。Indexing the XML column benefits query performance.

範例保留與 XML 資料完全相同的複本Example: Retaining Exact Copies of XML Data

舉例來說,假設政府規定您要保留與您的 XML 文件完全相同的原文副本。For illustration, assume that government regulations require you to retain exact textual copies of your XML documents. 例如,這些可能是簽署的文件、法律文件或股票交易訂單。For example, these could include signed documents, legal documents, or stock transaction orders. 您可能會想要將您的文件儲存在 [n]varchar(max) 資料行中。You may want to store your documents in a [n]varchar(max) column.

若要查詢,請在執行階段將資料轉換成 xml 資料類型,並對其執行 Xquery。For querying, convert the data to xml data type at run time and execute Xquery on it. 執行階段的轉換作業可能會很耗費資源,尤其是當文件很大時。The run-time conversion may be costly, especially when the document is large. 若您經常查詢,您可以另外將文件儲存在 xml 資料類型資料行中,當您從 [n]varchar(max) 資料行傳回完全相同的文件副本時,再加以檢索。If you query frequently, you can redundantly store the documents in an xml data type column and index it while you return exact document copies from the [n]varchar(max) column.

XML 資料行可能是以 [n]varchar(max) 資料行為基礎的計算資料行。The XML column may be a computed column, based on the [n]varchar(max) column. 但是您不能在計算的 XML 資料行上建立 XML 索引,也不能在 [n]varchar(max)varbinary(max) 資料行上建立 XML 索引。However, you cannot create an XML index on a computed, XML column, nor can an XML index be built on [n]varchar(max) or varbinary(max) columns.

XML 檢視技術XML View Technology

在您的 XML 結構描述與資料庫中的資料表之間定義對應,即可建立永續性資料的「XML 檢視」。By defining a mapping between your XML schemas and the tables in a database, you create an "XML view" of your persistent data. 藉由 XML 檢視,可使用 XML 大量載入功能來擴展基礎資料表。XML bulk load can be used to populate the underlying tables by using the XML view. 您可以使用 XPath 1.0 版來查詢 XML 檢視;該查詢會在資料表上轉換成 SQL 查詢。You can query the XML view by using XPath version 1.0; the query is translated to SQL queries on the tables. 同樣地,更新內容也會傳播至那些資料表。Similarly, updates are also propagated to those tables.

在下列狀況下,這項技術非常有用:This technology is useful in the following situations:

  • 您想要有一個以 XML 為中心的程式設計模型,透過現有的關聯式資料來使用 XML 檢視。You want to have an XML-centric programming model using XML views over your existing relational data.

  • 您有一個用於 XML 資料的結構描述 (XSD、XDR),是由外部協力廠商提供的。You have a schema (XSD, XDR) for your XML data that an external partner may have provided.

  • 在您的資料中,順序並不重要,或者您的查詢資料表資料不是遞迴式的,或者事先已知道可達到的最大遞迴深度。Order is not important in your data, or your query table data is not recursive, or the maximal recursion depth is known in advance.

  • 您想要使用 XPath 1.0 透過 XML 檢視來查詢及修改資料。You want to query and modify the data through the XML view by using XPath version 1.0.

  • 您想要使用 XML 檢視來大量載入 XML 資料,並將其分解在基礎資料表中。You want to bulk load XML data and decompose them into the underlying tables by using the XML view.

範例包括在資料交換及 Web 服務中公開為 XML 的關聯式資料,以及含有固定結構描述的 XML 資料。Examples include relational data exposed as XML for data exchange and Web services, and XML data with fixed schema. 如需詳細資訊,請參閱 MSDN Online LibraryFor more information, see the MSDN Online Library.

範例使用註解式 XML 結構描述 (AXSD) 將資料模型化Example: Modeling Data Using an Annotated XML Schema (AXSD)

舉例來說,假設您要將現有的關聯式資料 (例如:客戶、訂單及線性項目) 處理成 XML。For illustration, assume that you have existing relational data, such as customers, orders, and line items, that you want to handle as XML. 在關聯式資料上使用 AXSD,以定義 XML 檢視。Define an XML view by using AXSD over the relational data. XML 檢視可讓您將 XML 資料大量載入至資料表中,並使用 XML 檢視來查詢及更新關聯式資料。The XML view allows you to bulk load XML data into your tables and query and update the relational data by using the XML view. 如果您必須在不干擾 SQL 應用程式工作的情況下,與其他應用程式交換含有 XML 標記的資料,這種模型會很有用。This model is useful if you have to exchange data that contains XML markup with other applications while your SQL applications work uninterrupted.

混合模型Hybrid Model

關聯式與 xml 資料類型資料行的組合最常適用於資料的模型化。Frequently, a combination of relational and xml data type columns is appropriate for data modeling. XML 資料中有些值可以儲存在關聯式資料行中,其餘的值 (或是整個 XML 值) 則可儲存在 XML 資料行中。Some of the values from your XML data can be stored in relational columns, and the rest, or the whole XML value stored in an XML column. 這樣可能會產生較佳的效能,因為對於以關聯式資料行及鎖定特性來建立的索引,您會有比較大的控制權。This may yield better performance in that you have more control over the indexes created on the relational columns and locking characteristics.

至於要將哪些值儲存在關聯式資料行中,則需視您的工作負載而定。The values to store in relational columns depend on your workload. 例如,若您是依據路徑運算式 /Customer/@CustId 來擷取所有的 XML 值,則將 CustId 屬性值升級至關聯式資料行中並加以檢索,可能會產生較快的查詢效能。For example, if you retrieve all the XML values based on the path expression, /Customer/@CustId, promoting the value of the CustId attribute into a relational column and indexing it may yield faster query performance. 另一方面,如果 XML 資料是廣泛且毫不多餘地分解在關聯式資料行中,則重組的成本可能會很大。On the other hand, if your XML data is extensively and nonredundantly decomposed into relational columns, the re-assembly cost may be significant.

例如,針對高度結構化的 XML 資料,資料表的內容都已轉換成 XML;您可以將所有值對應到關聯式資料行,可能還會用到 XML 檢視技術。For highly structured XML data, for example, the content of a table has been converted into XML; you can map all values to relational columns, and possibly use XML view technology.

XML 資料的資料粒度Granularity of XML Data

儲存在 XML 資料行中之 XML 資料的資料粒度對於鎖定動作是很重要的,姑且不論這一點,它對於更新也是很重要的。The granularity of the XML data stored in an XML column is very important for locking and, to a lesser degree, it is also important for updates. SQL ServerSQL Server 針對 XML 和非 XML 資料都是使用相同的鎖定機制。uses the same locking mechanism for both XML and non-XML data. 因此,資料列層級的鎖定會導致資料列中所有的 XML 執行個體被鎖定。Therefore, row-level locking causes all XML instances in the row to be locked. 若資料粒度大,則鎖定大型 XML 執行個體來進行更新時,將會導致在多使用者情況下的輸送量降低。When the granularity is large, locking large XML instances for updates causes throughput to decline in a multiuser scenario. 另一方面,嚴重的分解也會失去物件封裝,並增加重組成本。On the other hand, severe decomposition loses object encapsulation and increases reassembly cost.

在滿足資料模型化需求與鎖定和更新特性之間取得平衡,對於良好的設計是很重要的。A balance between data modeling requirements and locking and update characteristics is important for good design. 然而,在 SQL ServerSQL Server中,實際儲存的 XML 執行個體大小就沒那麼重要了。However, in SQL ServerSQL Server, the size of actual stored XML instances is not as critical.

例如,對 XML 執行個體進行更新時,是使用對部份二進位大型物件 (BLOB) 和部份索引更新的新支援,其中會將目前已儲存的 XML 執行個體與其更新版本做比較。For example, updates to an XML instance are performed by using new support for partial binary large object (BLOB) and partial index updates in which the existing stored XML instance is compared to its updated version. 部份二進位大型物件 (BLOB) 更新作業會在這二個 XML 執行個體之間執行差異比較,並且只更新差異的部份。Partial binary large object (BLOB) update performs a differential comparison between the two XML instances and updates only the differences. 部分索引更新作業只會修改那些必須在 XML 索引中變更的資料列。Partial index updates modify only those rows that must be changed in the XML index.

xml 資料類型的限制Limitations of the xml Data Type

請注意下列適用於 xml 資料類型的一般限制:Note the following general limitations that apply to the xml data type:

  • xml 資料類型執行個體的預存表示法不能超過 2 GB。The stored representation of xml data type instances cannot exceed 2 GB.

  • 它無法當作 sql_variant 執行個體的子類型使用。It cannot be used as a subtype of a sql_variant instance.

  • 它不支援轉換 (Cast 或 Convert) 為 textntextIt does not support casting or converting to either text or ntext. 改用 varchar(max)nvarchar(max)Use varchar(max) or nvarchar(max) instead.

  • 它無法加以比較或排序。It cannot be compared or sorted. 這表示 xml 資料類型無法用在 GROUP BY 陳述式中。This means an xml data type cannot be used in a GROUP BY statement.

  • 它無法當作 ISNULL、COALESCE 和 DATALENGTH 以外之任何純量、內建函數的參數。It cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.

  • 它無法當作索引中的索引鍵資料行使用。It cannot be used as a key column in an index. 但是,在建立非叢集索引時使用 INCLUDE 關鍵字,可以將它包含在叢集索引中做為資料,或明確地將它加入非叢集索引中。However, it can be included as data in a clustered index or explicitly added to a nonclustered index by using the INCLUDE keyword when the nonclustered index is created.

  • XML 項目所建立的巢狀結構最多可以有 128 個層級。XML elements can be nested up to 128 levels.

另請參閱See Also

大量匯入與匯出 XML 文件的範例 (SQL Server)Examples of Bulk Import and Export of XML Documents (SQL Server)