How does this derived table with cross apply work?

What you`re seeing is the XQuery implementation in SQL Server . Although XQuery uses its own parser and performs its own algebrarization during the query compilation stage, the results are combined and optimized together with the DML portion of the query, then combined into a single execution plan.

SQL Server supports five different methods. value , exist , query , and nodes are used to access and transform the data. Last one, modify , uses XML DML to modify the data.

The value() method returns a scalar value from the XML instance Lets say you have the xml : `

declare @X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>’

select @X.value('/Order[1]/Customer[1]/@Id','int')`or
select @X.value('(/Order/Customer/@Id)[1]','int')

Would give you ID of the first customer from the first order

In your particular case value('.') means give me all values from the shredded element( that i will talk about in a moment) Remember in using value function you`re moving through XML. Now to make it more easier ,not to 'move' too much you can use function node which shreds XML into relational data. It returns a row set with rows representing the nodes identified by the path expression.

Example:

declare @X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>'


select C.t.value('(@Id)[1]','int')
from  @X.nodes('/Order/Customer') as C(t)

When you use the nodes() method with the XML column from the table, you must use the APPLY operator.

Example:

declare @X as table (xmlCode varchar(1000))
insert into @X values (
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>');

WITH CTE AS(
select CAST(xmlCode as XML) as X from @X
)
select C.t.value('(@Id)[1]','int')
from  CTE
CROSS APPLY X.nodes('/Order/Customer') as C(t)

I used examples on value and node, because you provided the code with those two functions only, if you want to read more about it please visit this

Hope this simple examples give you an idea of how to query xml types

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element <I></I> for the XML arrays constructed within the XMLTaggedList CTE statement. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group.

I find it easier to digest if I break up the query. Maybe you will as well.

DECLARE @dimDiagnosisGroup TABLE (dxGroupKey int, dxCodeList nvarchar(1024))
insert into @dimDiagnosisGroup
values  (1,'042, 070.70, 722.10'),
        (2,'042, 070.70, 780.52, 496, 716.90, 581.9'),
        (3,'042, 070.70, 782.1, V58.69'),
        (4,'042, 070.70, 782.3, V58.69')

-- Construct the XML arrays, this is what's going on within the CTE
SELECT dxGroupKey,
CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
    AS Diagnosis_Code_List
FROM @dimDiagnosisGroup

-- Extract all elements for each group
 ;WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM @dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            -- Extract the Value from the <I></I> element and cast it as a VARCHAR(MAX)
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        -- Seperate each <I></I> element into it's own row
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)

An addendum to John Eisbrener's answer:

As far as the ExtractedDiagnosisList(X) part, it might help some of us if there as a space in it:

ExtractedDiagnosisList (X)

From the SQL docs, derived tables (and rowset functions, and @variable. function_calls) allow you to not only specify a table alias, but a list of column aliases for the columns returned in the "table" in question. In this case, there's one column, and we're going to call it X.

See?

[ FROM { } [ ,...n ] ]
::=
{

...

| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]

...

| derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]

...

| @variable.function_call ( expression [ ,...n ] )
[ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]

...