Create instances of XML data
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This article describes how to generate XML instances.
In SQL Server, you can generate XML instances in the following ways:
- Type casting string instances.
- Using the
SELECTstatement with theFOR XMLclause. - Using constant assignments.
- Using bulk load.
Type cast string and binary instances
You can parse any of the SQL Server string data types, such as [n]varchar, [n]char, [n]text, varbinary, and image, into the xml data type by casting (CAST) or converting (CONVERT) the string to the xml data type. Untyped XML is checked to confirm that it's well formed. If there's a schema associated with the xml type, validation is also performed. For more information, see Compare typed XML to untyped XML.
XML documents can be encoded with different encodings (for example, UTF-8, UTF-16, Windows-1252). The following outlines the rules on how the string and binary source types interact with the XML document encoding and how the parser behaves.
Since nvarchar assumes a 2-byte Unicode encoding such as UTF-16 or UCS-2, the XML parser treats the string value as a 2-byte Unicode encoded XML document or fragment. The XML document needs to be encoded in a 2-byte Unicode encoding as well to be compatible with the source data type. A UTF-16 encoded XML document can have a UTF-16 byte order mark (BOM), but it doesn't need to, since the context of the source type makes it clear that it can only be a 2-byte Unicode encoded document.
The content of a varchar string is treated as a 1-byte encoded XML document/fragment by the XML parser. Since the varchar source string has a code page associated, the parser uses 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 reports an error.
The content of varbinary is treated as a codepoint stream that is passed directly to the XML parser. Thus, the XML document or fragment needs to provide the BOM or other encoding information inline. The parser only looks at the stream to determine the encoding. This means that UTF-16 encoded XML needs to provide the UTF-16 BOM, and an instance without BOM and without a declaration encoding, is interpreted as UTF-8.
If the encoding of the XML document isn't known in advance and the data is passed as string or binary data instead of XML data before casting to XML, you should treat the data as varbinary. 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 Server internally represents XML in an efficient binary representation that uses UTF-16 encoding. User-provided encoding isn't preserved, but is considered during the parse process.
Type cast CLR user-defined types
If a CLR user-defined type has an XML Serialization, instances of that type can be explicitly cast to an XML data type. For more information about the XML serialization of a CLR user-defined type, see XML Serialization from CLR Database Objects.
Handle white space in typed XML
In SQL Server, white space inside element content is ignored if it occurs inside a sequence of white-space-only character data delimited by markup, such as begin or end tags, and isn't entitized. (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). This is because the XML parser in SQL Server recognizes only a limited number of DTD subsets, as defined in XML 1.0. For more information about the limited DTD subsets supported in SQL Server, see CAST and CONVERT.
By default, the XML parser discards insignificant white space when it converts string data to XML if either of the following options is true:
The
xml:spaceattribute isn't defined on an element or its ancestor elements.The
xml:spaceattribute 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;
Here's the result set.
<root><child/></root>
However, you can change this behavior. 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);
If the style parameter is either not used, or its value is set to 0, insignificant white space isn't preserved for the conversion of the xml DT instance. 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.
Example: Cast a string value to typed XML and assign it to a column
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>';
The following insert operation implicitly converts from a string to the xml type:
INSERT INTO T
VALUES (3, @s);
You can explicitly CAST the string to the xml type:
INSERT INTO T
VALUES (3, CAST(@s AS XML));
Or you can use CONVERT, as shown in the following code sample:
INSERT INTO T
VALUES (3, CONVERT(XML, @s));
Example: Convert a string to typed XML and assign it to a variable
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;
Use the SELECT statement with a FOR XML clause
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
)
...;
The SELECT statement returns a textual XML fragment that is then parsed during the assignment to the xml data type variable.
You can also use the TYPE directive in FOR XML queries 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;
Here's the result set.
<Production.ProductModel ProductModelID="19" Name="Mountain-100" />...
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 more information about FOR XML, see FOR XML (SQL Server).
Note
SQL Server 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 Server Database Engine columns, variables, and output parameters. In client application code, the ADO.NET provider requests that this xml data type information be sent in a binary encoding from the server. However, if you're using FOR XML without the TYPE directive, the XML data returns as a string type. In any case, the client provider will always be able to handle either form of XML.
Use constant assignments
A string constant can be used where an instance of the xml data type is expected. This assignment 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/>';
The previous example implicitly converts the string to the xml data type and assigns it to an xml type variable.
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>');
Note
For typed XML, the XML is validated against the specified schema. For more information, see Compare typed XML to untyped XML.
Use bulk load
The enhanced OPENROWSET functionality allows you to bulk load XML documents in the database. You can bulk load XML instances from files into the xml type columns in the database. For working samples, see Examples of bulk import and export of XML documents (SQL Server). For more information about loading XML documents, see Load XML data.
In this section
| Article | Description |
|---|---|
| XML query options and preserved data | Describes the parts of XML instances that aren't preserved when they're stored in databases. |