建立 XML 資料的執行個體Create Instances of XML Data

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

這個主題描述如何產生 XML 執行個體。This topic describes how to generate XML instances.

SQL ServerSQL Server中,您可以用下列方式產生 XML 執行個體:In SQL ServerSQL Server, you can generate XML instances in the following ways:

  • 類型轉換字串執行個體。Type casting string instances.

  • 使用含有 FOR XML 子句的 SELECT 陳述式。Using the SELECT statement with the FOR XML clause.

  • 使用常數指派。Using constant assignments.

  • 使用大量載入。Using bulk load.

類型轉換字串和二進位執行個體Type Casting String and Binary Instances

您可以將字串轉換 (CAST 或 CONVERT) 為 SQL ServerSQL Server xml資料類型,以便將任何字串資料類型 (例如 [n][var] char[n]textvarbinaryimage ) 剖析至 xml 資料類型。You can parse any of the SQL ServerSQL Server string data types, such as [n][var]char, [n]text, varbinary,and image, into the xml data type by casting (CAST) or converting (CONVERT) the string to the xml data type. 將會檢查不具類型的 XML 以確認它的格式正確。Untyped XML is checked to confirm that it is well formed. 如果有與 xml 類型相關聯的結構描述,也會執行驗證。If there is a schema associated with the xml type, validation is also performed. 如需詳細資訊,請參閱 比較具類型的 XML 與不具類型的 XMLFor more information, see Compare Typed XML to Untyped XML.

XML 文件可以使用不同的編碼 (例如,UTF-8、UTF-16、windows-1252) 加以編碼。XML documents can be encoded with different encodings (for example, UTF-8, UTF-16, windows-1252). 以下是字串與二進位來源類型如何與 XML 文件編碼互動以及剖析器作用方式的規則。The following outlines the rules on how the string and binary source types interact with the XML document encoding and how the parser behaves.

由於 nvarchar 假設使用兩個位元組的 Unicode 編碼 (例如 UTF-16 或 UCS-2),因此 XML 剖析器會將字串值視為兩個位元組的 Unicode 編碼 XML 文件或片段。Since nvarchar assumes a two-byte unicode encoding such as UTF-16 or UCS-2, the XML parser will treat the string value as a two-byte Unicode encoded XML document or fragment. 這表示必須將 XML 文件以兩個位元組的 Unicode 編碼加以編碼,才能與來源資料類型相容。This means that the XML document needs to be encoded in a two-byte Unicode encoding as well to be compatible with the source data type. 雖然 UTF-16 編碼的 XML 文件可有 UTF-16 位元組順序標示 (BOM),但它並不需要,因為來源類型的內容已經清楚表示,它只能為兩個位元組的 Unicode 編碼文件。A UTF-16 encoded XML document can have a UTF-16 byte order mark (BOM), but it does not need to, since the context of the source type makes it clear that it can only be a two-byte Unicode encoded document.

XML 剖析器會將 varchar 字串的內容視為一個位元組編碼的 XML 文件/片段。The content of a varchar string is treated as a one-byte encoded XML document/fragment by the XML parser. 由於 varchar 來源字串有相關聯的字碼頁,當 XML 本身未明確指定編碼時,剖析器將使用該字碼頁進行編碼。如果 XML 執行個體具有 BOM 編碼宣告,此 BOM 或宣告必須與字碼頁一致,否則剖析器將會報告錯誤。Since the varchar source string has a code page associated, the parser will use that code page for the encoding if no explicit encoding is specified in the XML itself If an XML instance has a BOM or an encoding declaration, the BOM or declaration needs to be consistent with the code page, otherwise the parser will report an error.

varbinary 的內容會被視為是直接傳送至 XML 剖析器的字碼指標資料流。The content of varbinary is treated as a codepoint stream that is passed directly to the XML parser. 因此,XML 文件或片段必須以內嵌方式提供 BOM 或其他編碼資訊。Thus, the XML document or fragment needs to provide the BOM or other encoding information inline. 剖析器只會查看資料流來決定編碼。The parser will only look at the stream to determine the encoding. 這表示 UTF-16 編碼的 XML 必須提供 UTF-16 BOM,不具有 BOM 和宣告編碼的執行個體將被解譯為 UTF-8。This means that UTF-16 encoded XML needs to provide the UTF-16 BOM and an instance without BOM and without a declaration encoding will be interpreted as UTF-8.

如果預先不知道 XML 文件的編碼,且在轉換成 XML 之前,將資料傳送為字串或二進位資料而非 XML 資料,則建議將資料視為 varbinaryIf the encoding of the XML document is not known in advance and the data is passed as string or binary data instead of XML data before casting to XML, it is recommended to treat the data as varbinary. 例如,使用 OpenRowset() 讀取 XML 檔案的資料時,必須將要讀取的資料指定為 varbinary(max) 值:For example, when reading data from an XML file using OpenRowset(), one should specify the data to be read as a varbinary(max) value:

select CAST(x as XML)   
from OpenRowset(BULK 'filename.xml', SINGLE_BLOB) R(x)  

SQL ServerSQL Server 在內部以 UTF-16 編碼之有效率的二進位表示法來表示 XML。internally represents XML in an efficient binary representation that uses UTF-16 encoding. 不會保留使用者提供的編碼,但是會在剖析過程中考慮該編碼。User-provided encoding is not preserved, but is considered during the parse process.

類型轉換 CLR 使用者定義型別Type Casting CLR user-defined types

如果 CLR 使用者定義型別具有 XML 序列化,即可將該類型的執行個體明確轉換成 XML 資料類型。If a CLR user-defined type has an XML Serialization, instances of that type can be explicitly cast to an XML datatype. 如需有關 CLR 使用者定義型別之 XML 序列化的詳細資料,請參閱 從 CLR 資料庫物件進行 XML 序列化For more details about the XML serialization of a CLR user-defined typed, see XML Serialization from CLR Database Objects.

以具類型的 XML 處理空白White Space Handling in Typed XML

SQL ServerSQL Server 中,元素內容中的空白如果發生在以標記 (如開始或結束標記) 所分隔的僅空白字元資料序列內且未實體化,則會將它視為無意義。In SQL ServerSQL Server, white space inside element content is considered insignificant if it occurs inside a sequence of white-space-only character data delimited by markup, such as begin or end tags, and is not entitized. (將會忽略 CDATA 區段。)空白處理的方式與 XML 1.0 規格 (由全球資訊網協會 (W3C) 所發佈) 所述的空白處理方式不同。(CDATA sections are ignored.) This handling of white space handling is different from how white space is described in the XML 1.0 specification published by the World Wide Web Consortium (W3C). 這是因為 SQL ServerSQL Server 中的 XML 剖析器,只能辨識有限的 DTD 子集數目,如 XML 1.0 中所定義。This is because the XML parser in SQL ServerSQL Server recognizes only a limited number of DTD subsets, as defined in XML 1.0. 如需有關 SQL ServerSQL Server 中所支援之有限 DTD 子集的詳細資訊,請參閱 CAST 和 CONVERT (Transact-SQL)For more information about the limited DTD subsets supported in SQL ServerSQL Server, see CAST and CONVERT (Transact-SQL).

依預設,只要下列任一項為真,當 XML 剖析器將字串資料轉換成 XML 時,將會捨棄無意義的空白。By default, the XML parser discards insignificant white space when it converts string data to XML if either of the following is true:

  • xml:space 屬性並未在元素或其上階元素中定義。The xml:space attribute is not defined on an element or its ancestor elements.

  • 在某個元素或在其中一個它的上階元素生效的 xml:space 屬性,具有預設值。The xml:space attribute in effect on an element, or one of its ancestor elements, has the value of default.

例如:For example:

declare @x xml  
set @x = '<root>      <child/>     </root>'  
select @x   

以下是結果:This is the result:

<root><child/></root>  

然而,您可以變更此行為。However, you can change this behavior. 若要保留 XML DT 執行個體的空白字元,請使用 CONVERT 運算子並將其選擇性的 style 參數值設為 1。To preserve white space for an xml DT instance, use the CONVERT operator and its optional style parameter set to a value of 1. 例如:For example:

SELECT CONVERT(xml, N'<root>      <child/>     </root>', 1)  

如果未使用 style 參數或是將其值設為 0,在轉換 xml DT 執行個體時,將不會保留不重要的空白。If the style parameter is either not used or its value is set to 0, insignificant white space is not preserved for the conversion of the xml DT instance. 如需如何使用 CONVERT 運算子以及將字串資料轉換成 xmlDT 執行個體時其 style 參數的詳細資訊,請參閱 CAST 和 CONVERT (Transact-SQL)For more information about how to use the CONVERT operator and its style parameter when converting string data to xml DT instances, see CAST and CONVERT (Transact-SQL).

範例將字串值轉換成具類型的 xml 並將它指派給資料行Example: Cast a string value to typed xml and assign it to a column

下列範例將含有 XML 片段的字串變數轉換成 xml 資料類型,然後在 xml 類型資料行中予以儲存:The following example casts a string variable that contains an XML fragment to the xml data type and then stores it in the xml type column:

CREATE TABLE T(c1 int primary key, c2 xml)  
go  
DECLARE  @s varchar(100)  
SET @s = '<Cust><Fname>Andrew</Fname><Lname>Fuller</Lname></Cust>'   

下列插入作業會將字串隱含轉換成 xml 類型:The following insert operation implicitly converts from a string to the xml type:

INSERT INTO T VALUES (3, @s)   

您可以明確地使用 cast() 將字串轉換成 xml 類型:You can explicitly cast() the string to the xml type:

INSERT INTO T VALUES (3, cast (@s as xml))  

或者您可以使用 convert(),如下列所示:Or you can use convert(), as shown in the following:

INSERT INTO T VALUES (3, convert (xml, @s))   

範例將字串轉換成具類型的 xml 並將它指派給變數Example: Convert a string to typed xml and assign it to a variable

在下列範例中,字串會轉換成 xml 類型,並受指派給 xml 的變數。In the following example, a string is converted to xml type and assigned to a variable of the xml data type:

declare @x xml  
declare  @s varchar(100)  
SET @s = '<Cust><Fname>Andrew</Fname><Lname>Fuller</Lname></Cust>'   
set @x =convert (xml, @s)  
select @x  

使用含有 FOR XML 子句的 SELECT 陳述式Using the SELECT Statement with a FOR XML Clause

您可以在 SELECT 陳述式中使用 FOR XML 子句以傳回 XML 的結果。You can use the FOR XML clause in a SELECT statement to return results as XML. 例如:For example:

DECLARE @xmlDoc xml  
SET @xmlDoc = (SELECT Column1, Column2  
               FROM   Table1, Table2  
               WHERE   Some condition  
               FOR XML AUTO)  
 ...  

SELECT 陳述式會傳回文字 XML 片段,該片段會在指派期間剖析成 xml 資料類型變數。The SELECT statement returns a textual XML fragment that is then parsed during the assignment to the xml data type variable.

您也可以在 FOR XML 子句中使用 TYPE 指示詞 ,該子句會直接以 xml 類型傳回 FOR XML 查詢結果:You can also use the TYPE directive in the FOR XML clause that directly returns a FOR XML query result as xml type:

Declare @xmlDoc xml  
SET @xmlDoc = (SELECT ProductModelID, Name  
               FROM   Production.ProductModel  
               WHERE  ProductModelID=19  
               FOR XML AUTO, TYPE)  
SELECT @xmlDoc  

以下是結果:This is the result:

<Production.ProductModel ProductModelID="19" Name="Mountain-100" />...  

在下列範例中,FOR XML 查詢之具類型的 xml 結果會插入 xml 類型資料行:In the following example, the typed xml result of a FOR XML query is inserted into an xml type column:

CREATE TABLE T1 (c1 int, c2 xml)  
go  
INSERT T1(c1, c2)  
SELECT 1, (SELECT ProductModelID, Name  
           FROM Production.ProductModel  
           WHERE ProductModelID=19  
           FOR XML AUTO, TYPE)  
SELECT * FROM T1  
go  

如需 FOR XML 的詳細資訊,請參閱 FOR XML (SQL Server)For more information about FOR XML, see FOR XML (SQL Server).

注意

SQL ServerSQL Server xml 資料類型執行個體傳回用戶端,作為不同伺服器建構的結果 (例如使用 TYPE 指示詞的 FOR XML 查詢),或者使用 xml 資料類型從 SQL 資料行、變數和輸出參數傳回 XML。returns xml data type instances to the client as a result of different server constructs such as FOR XML queries that use the TYPE directive, or where the xml data type is used to return XML from SQL columns, variables, and output parameters. 在用戶端應用程式的程式碼中,ADO.NET 提供者會要求從伺服器以二進位編碼傳送這項 xml 資料類型資訊。In client application code, the ADO.NET provider requests that this xml data type information be sent in a binary encoding from the server. 但若您使用的 FOR XML 不含 TYPE 指示詞,XML 資料就會以字串類型傳回。However, if you are using FOR XML without the TYPE directive, the XML data returns as a string type. 在任一情況下,用戶端提供者將永遠可以處理任一 XML 形式。In any case, the client provider will always be able to handle either form of XML.

使用常數指派Using Constant Assignments

在應有 xml 資料類型的執行個體中可以使用字串常數。A string constant can be used where an instance of the xml data type is expected. 這與使用 CAST 將字串隱含轉換成 XML 是相同的。This is the same as an implied CAST of string to XML. 例如:For example:

DECLARE @xmlDoc xml  
SET @xmlDoc = '<Cust><Fname>Andrew</Fname><Lname>Fuller</Lname></Cust>'   
-- Or  
SET @xmlDoc = N'<?xml version="1.0" encoding="ucs-2"?><doc/>'  

上述範例將字串隱含轉換成 xml 資料類型,並將其指派給 xml 類型變數。The previous example implicitly converts the string to the xml data type and assigns it to an xml type variable.

下列範例會將常數字串插入 xml 類型資料行:The following example inserts a constant string into an xml type column:

CREATE TABLE T(c1 int primary key, c2 xml)  
INSERT INTO T VALUES (3, '<Cust><Fname>Andrew</Fname><Lname>Fuller</Lname></Cust>')   

注意

對於具類型的 XML,將會針對指定的結構描述驗證 XML。For typed XML, the XML is validated against the specified schema. 如需詳細資訊,請參閱 比較具類型的 XML 與不具類型的 XMLFor more information, see Compare Typed XML to Untyped XML.

使用大量載入Using Bulk Load

增強的 OPENROWSET (Transact-SQL) 功能,可讓您在資料庫中大量載入 XML 文件。The enhanced OPENROWSET (Transact-SQL) functionality allows you to bulk load XML documents in the database. 您可以從檔案將 XML 執行個體大量載入資料庫中的 xml 類型資料行。You can bulk load XML instances from files into the xml type columns in the database. 如需實用範例,請參閱大量匯入與匯出 XML 文件的範例 (SQL Server)For working samples, see Examples of Bulk Import and Export of XML Documents (SQL Server). 如需有關載入 XML 文件的詳細資訊,請參閱載入 XML 資料For more information about loading XML documents, see Load XML Data.

本節內容In This Section

主題Topic DescriptionDescription
擷取及查詢 XML 資料Retrieve and Query XML Data 描述當 XML 執行個體儲存於資料庫中時,未保留的 XML 執行個體部分。Describes the parts of XML instances that are not preserved when they are stored in databases.

另請參閱See Also

比較具類型的 XML 與不具類型的 XML Compare Typed XML to Untyped XML
xml 資料類型方法 xml Data Type Methods
XML 資料修改語言 (XML DML) XML Data Modification Language (XML DML)
XML 資料 (SQL Server)XML Data (SQL Server)