Tips for using XML in SQL Server 2016
Consider using the RAW mode of FOR XML queries, instead of AUTO or EXPLICIT modes.
The RAW mode transforms each row in the result set into an XML element with a generic
identifier
performance, in comparison with the AUTO and EXPLICIT modes, but is not so informative
as AUTO or EXPLICIT modes.
Use the “large value types out of row” table option to store XML data in the table.
In order to control the behavior of how XML data is stored in the table we have 2 options:
the “text in row” and the “large value types out of row” table options. The “text in row”
feature will be removed in a future version of SQL Server, so you should use the
“large value types out of row” table option to store XML data. You can use the
sp_tableoption system stored procedure to set the “large value types out of row”
table option.
Consider replacing OPENXML with the XML nodes() function.
The nodes() function is useful when you want to shred an xml data type instance into
relational data. The result of the nodes() function is a rowset that contains logical
copies of the original XML instances.
Try to avoid using the XMLDATA option with your FOR XML clause.
The XMLDATA option returns the schema data. The XMLDATA option returns the data type for
each columns used in your query. If you do not need the information of the columns data
type in the query result set, try to avoid using the XMLDATA option with your FOR XML
clause, because using this option adds extra overhead to your server and reduce the
total SQL Server performance.
Consider using the XML data type.
This data type is used to store XML documents in table columns or Transact-SQL variables.
The XML data type can be used in variables, columns, or in stored procedure and function
parameters.
Use the XQuery value() method instead of the query() method when you want to return
event data.
Using the XQuery value() method instead of the query() method may be more efficient because
the query() method returns XML and ampersand-escaped carriage return and line feed (CR/LF)
instances in the output, while the XQuery value() method renders CR/LF instances invisible
in the output.
Consider using XML format files to bulk import data into tables or non-partitioned views
and to bulk export data.
SQL Server 2016 supports the XML format file. XML format files are more flexible and powerful
than non-XML format files and easy to understand and create. So, consider using XML format
files instead of non-XML format files.
Use UTF-16 supplementary characters (SC) collation option for XML.
SQL Server 2016 supports – “SC” (supplementary characters) collation option that
identifies whether a collation is UTF-16-aware. You can use this collation option in the
SQL Types XML schema.
Consider using TYPE directive in FOR XML queries to return the result of a FOR XML
query as XML data type.
In SQL Server 2016, the result of a FOR XML query can be stored in a column, stored
procedure parameter, or variable defined using the XML data type.
Use XML Data Manipulation Language (XML DML) if you need to insert, update, and delete
XML documents or fragments.
In comparison with the current W3C definition of XQuery that does not include the ability
to modify XML documents, SQL Server 2016 has extended the XQuery implementation to include
the ability to insert, update, and delete XML documents or fragments. So, if you need to
modify XML documents, you can use XML DML in SQL Server 2016.
You can build, rebuild or drop XML indexes online.
In SQL Server 2016, indexes that include columns of large object (LOB) data (varchar(max),
nvarchar(max), varbinary(max), XML data) can be built, rebuilt, or dropped online.
Consider using the AUTO mode of FOR XML queries, instead of the EXPLICIT modes.
The AUTO mode returns query results as nested XML elements and maps the table columns to XML
attributes. The AUTO type offers the next best overall performance after the RAW mode. The
EXPLICIT mode used when you need the additional information in the shape of the resulting XML
tree. The EXPLICIT mode specifies that additional information about the desired nesting will
be specified explicitly. This mode provides the slowest performance and used, for example,
when the application accept data in a predefined format.
Use XQuery for querying XML data stored using the XML data type.
SQL Server 2016 supports XQuery language that is based on working drafts of the World Wide
Web Consortium (W3C) XQuery language definitions. This query language is used for querying
XML data stored using the XML data type.
Consider creating indexes on XML columns.
SQL Server 2016 supports indexes on XML columns. XML data stored in XML data type columns
as large binary objects, which size can be up to 2 GB. Without an index, the XML instances
are shredded at run time to evaluate a query. Using index on a XML column allows the
Database Engine to more efficiently process the documents and fragments.