Database Reference
In-Depth Information
Listing 11-20.
Generating comma-separated list of values with FOR XML PATH
select LEFT(Data,LEN(Data) - 1) -- removing right-most comma from
(
select
CONVERT(varchar(max),
(
select RecId as [text()], ',' as [text()]
from dbo.Data
for XML PATH('')
)
) as Data
) List
This approach is very fast compared to regular string concatenation in the code. You need to be careful however,
as SQL Server replaces characters with character entities when needed. For example, it would replace the
<
character
with
<
if it is present.
For more information about the
FOR XML
clause and the shape of the XML it generates, read this article:
Summary
While XML adds flexibility to the data model, it comes at a high cost. Queries against XML Data are slower and
more CPU-intensive than queries against relational data. You can improve XQuery performance with XML indexes,
although they require a large amount of storage space—often several times larger than that of the XML data itself.
It is recommended that you create a primary XML index when the XML data is relatively static and index
maintenance does not introduce a lot of overhead; XML data is queried often enough and, you will have enough
storage space to accommodate the index. Secondary XML indexes, which are non-clustered indexes on the primary
XML index internal table, can be useful for optimizing specific query patterns in the code.
You can make XML typed by specifying that the XML conforms to the specific XML Schema collection. Queries
against typed XML are usually more efficient. Typed XML requires more storage space, because the XML data type
preserves type information, even though it reduces the size of the primary XML index, especially in the case of
element-centric XML. You need to consider the overhead of the schema validation before making typed XML.
There are several rules that you must follow for designing efficient XQuery and XPath expressions. One of the
biggest performance hits with untyped XML is the atomization of nodes. This introduces additional calls to XML
Reader table-valued functions. Moreover, descendent axes in the path, expressions at the middle of the path, and type
casts also negatively affect XQuery performance.
You must avoid property-container design patterns, where you store name/value pairs, such as
<props><name>color</name> <value>black</value></props>
, unless they are absolutely needed. The reason for
this is that property-container design patterns usually introduce expressions in the middle of the path when you
access the data stored in the
values
elements of such XML.
The most important decisions are made during design stage. You must evaluate whether XML needs to be used,
and then you must define what data should be stored in XML format. When data conforms to a relational model,
you will achieve better performance by shredding all or part of the data, and retaining the separate elements and
attributes as regular non-XML columns. While it is great to have flexibility in your system, you must remember that
nothing is free, and flexibility comes at the cost of performance.
