適用於:SQL Server
Azure SQL Database
Azure SQL 受控執行個體
Microsoft Fabric 中的 SQL 資料庫
此範例說明如何在使用 EXPLICIT 模式的 SELECT 陳述式中,使用 XMLTEXT 指示詞來存取溢位資料行中的資料。
假設有 Person 資料表。 此資料表有一個 Overflow 資料行,可用來儲存 XML 文件的未消耗部分。
直接連接到目標範例資料庫,並執行以下程式碼範例。
CREATE TABLE Person(PersonID varchar(5), PersonName varchar(20), Overflow nvarchar(200));
GO
INSERT INTO Person VALUES
('P1','Joe',N'<SomeTag attr1="data">content</SomeTag>')
,('P2','Joe',N'<SomeTag attr2="data"/>')
,('P3','Joe',N'<SomeTag attr3="data" PersonID="P">content</SomeTag>');
此查詢從 Person 資料表擷取資料行。 對於 Overflow 資料行,雖然未指定 AttributeName,但為了提供通用的資料表資料行名稱,會將 指示詞 設為 XMLTEXT。
SELECT 1 as Tag, NULL as parent,
PersonID as [Parent!1!PersonID],
PersonName as [Parent!1!PersonName],
Overflow as [Parent!1!!XMLTEXT] -- No AttributeName; XMLTEXT directive
FROM Person
FOR XML EXPLICIT;
在產生的 XML 文件中:
因為AttributeName未針對
Overflow資料欄指定,且已指定xmltext指示詞,所以<overflow>元素中的屬性會附加到外圍<Parent>元素的屬性清單。由於
PersonID元素中的<xmltext>屬性與PersonID屬性在同一個元素層級上擷取時發生衝突,因此即使<xmltext>為NULL,PersonID元素中的該屬性也會被忽略。 通常,某個屬性會覆寫溢位區段中同名的屬性。
以下是結果:
<Parent PersonID="P1" PersonName="Joe" attr1="data">content</Parent>
<Parent PersonID="P2" PersonName="Joe" attr2="data"></Parent>
<Parent PersonID="P3" PersonName="Joe" attr3="data">content</Parent>
如果溢位資料有子元素而且指定相同的查詢,則 Overflow 資料行中的子元素將會新增為封閉式 <Parent> 元素的子元素。
例如,變更 Person 資料表中的資料,讓 Overflow 資料行現在擁有子元素。 直接連接到目標範例資料庫,並執行以下程式碼範例。
TRUNCATE TABLE Person;
GO
INSERT INTO Person VALUES
('P1','Joe',N'<SomeTag attr1="data">content</SomeTag>')
,('P2','Joe',N'<SomeTag attr2="data"/>')
,('P3','Joe',N'<SomeTag attr3="data" PersonID="P"><name>PersonName</name></SomeTag>');
如果執行相同的查詢,則 <xmltext> 元素中的子元素將會新增為封閉式 <Parent> 元素的子元素:
SELECT 1 as Tag, NULL as parent,
PersonID as [Parent!1!PersonID],
PersonName as [Parent!1!PersonName],
Overflow as [Parent!1!!XMLTEXT] -- no AttributeName, XMLTEXT directive
FROM Person
FOR XML EXPLICIT;
以下是結果:
<Parent PersonID="P1" PersonName="Joe" attr1="data">content</Parent>
<Parent PersonID="P2" PersonName="Joe" attr2="data"></Parent>
<Parent PersonID="P3" PersonName="Joe" attr3="data">
<name>PersonName</name>
</Parent>
如果使用 xmltext 指示詞指定 AttributeName,則 <overflow> 元素的屬性會新增為外層 <Parent> 元素之子元素的屬性。 為 AttributeName 所指定的名稱將成為子元素的名稱。
在此查詢中,AttributeName (<overflow>) 會與 xmltext 指示詞一起指定*:*
SELECT 1 as Tag, NULL as parent,
PersonID as [Parent!1!PersonID],
PersonName as [Parent!1!PersonName],
Overflow as [Parent!1!overflow!XMLTEXT] -- Overflow is AttributeName
-- XMLTEXT is a directive
FROM Person
FOR XML EXPLICIT;
以下是結果:
<Parent PersonID="P1" PersonName="Joe">
<overflow attr1="data">content</overflow>
</Parent>
<Parent PersonID="P2" PersonName="Joe">
<overflow attr2="data" />
</Parent>
<Parent PersonID="P3" PersonName="Joe">
<overflow attr3="data" PersonID="P">
<name>PersonName</name>
</overflow>
</Parent>
在此查詢元素中,會為 PersonName 屬性指定 directive。 這會導致 PersonName 被新增為外層 <Parent> 元素的子元素。
<xmltext> 的屬性仍然會附加到封閉式 <Parent> 元素。
<overflow> 元素的內容 (子元素) 將會附加到封閉式 <Parent> 元素的其他子元素前面。
SELECT 1 AS Tag, NULL as parent,
PersonID AS [Parent!1!PersonID],
PersonName AS [Parent!1!PersonName!element], -- element directive
Overflow AS [Parent!1!!XMLTEXT]
FROM Person
FOR XML EXPLICIT;
以下是結果:
<Parent PersonID="P1" attr1="data">content<PersonName>Joe</PersonName>
</Parent>
<Parent PersonID="P2" attr2="data">
<PersonName>Joe</PersonName>
</Parent>
<Parent PersonID="P3" attr3="data">
<name>PersonName</name>
<PersonName>Joe</PersonName>
</Parent>
如果 XMLTEXT 資料行資料包含根元素的屬性,這些屬性不會顯示於 XML 資料結構描述中,而且 MSXML 剖析器並不會驗證產生的 XML 文件片段。 例如:
SELECT 1 AS Tag,
0 AS Parent,
N'<overflow a="1"/>' AS 'overflow!1!!xmltext'
FOR XML EXPLICIT, xmldata;
以下是結果。 在傳回的結構描述中,結構描述已經遺漏 a 溢位屬性:
<Schema name="Schema2"
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="overflow" content="mixed" model="open">`
</ElementType>`
</Schema>`
<overflow xmlns="x-schema:#Schema2" a="1">
</overflow>