Example: Specify the XMLTEXT directive
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This example illustrates how data in the overflow column is addressed by using the XMLTEXT directive in a SELECT
statement using EXPLICIT mode.
Consider the Person
table. This table has an Overflow
column that stores the unconsumed part of the XML document.
USE tempdb;
GO
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>');
This query retrieves columns from the Person
table. For the Overflow
column, AttributeName isn't specified, but directive is set to XMLTEXT
as part of providing a universal table column name.
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;
In the resulting XML document:
Because AttributeName isn't specified for the
Overflow
column and thexmltext
directive is specified, the attributes in the<overflow>
element are appended to the attribute list of the enclosing<Parent>
element.Because the
PersonID
attribute in the<xmltext>
element conflicts with thePersonID
attribute retrieved on the same element level, the attribute in the<xmltext>
element is ignored, even ifPersonID
is NULL. Generally, an attribute overrides an attribute of the same name in the overflow.
This is the result:
<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>
If the overflow data has subelements and the same query is specified, the subelements in the Overflow
column are added as the subelements of the enclosing <Parent>
element.
For example, change the data in the Person
table so that the Overflow
column now has subelements.
USE tempdb;
GO
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>');
If the same query is executed, the subelements in the <xmltext>
element are added as subelements of the enclosing <Parent>
element:
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;
This is the result:
<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>
If AttributeName is specified with the xmltext
directive, the attributes of the <overflow>
element are added as attributes of the subelements of the enclosing <Parent>
element. The name specified for AttributeName becomes the name of the subelement
In this query, AttributeName, <overflow>
, is specified together with the xmltext
directive*:*
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;
This is the result:
<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>
In this query element, directive is specified for PersonName
attribute. This results in PersonName
being added as a subelement of the enclosing <Parent>
element. The attributes of the <xmltext>
are still appended to the enclosing <Parent>
element. The contents of the <overflow>
element, subelements, are prepended to the other subelements of the enclosing <Parent>
elements.
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;
This is the result:
<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>
If the XMLTEXT
column data contains attributes on the root element, these attributes aren't shown in the XML data schema and the MSXML parser doesn't validate the resulting XML document fragment. For example:
SELECT 1 AS Tag,
0 AS Parent,
N'<overflow a="1"/>' AS 'overflow!1!!xmltext'
FOR XML EXPLICIT, xmldata;
This is the result. In the returned schema, the overflow attribute a
is missing from the schema:
<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>
See also
Váš názor
https://aka.ms/ContentUserFeedback.
Připravujeme: V průběhu roku 2024 budeme postupně vyřazovat problémy z GitHub coby mechanismus zpětné vazby pro obsah a nahrazovat ho novým systémem zpětné vazby. Další informace naleznete v tématu:Odeslat a zobrazit názory pro