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 =
'&lt;Order OrderId="42" OrderTotal="49.96"&gt;
&lt;Customer Id="123"/&gt;
&lt;OrderLineItems&gt;
&lt;OrderLineItem&gt;
&lt;ArticleId&gt;250&lt;/ArticleId&gt;
&lt;Quantity&gt;3&lt;/Quantity&gt;
&lt;Price&gt;9.99&lt;/Price&gt;
&lt;/OrderLineItem&gt;
&lt;/OrderLineItems&gt;
&lt;/Order&gt;’

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 =
'&lt;Order OrderId="42" OrderTotal="49.96"&gt;
&lt;Customer Id="123"/&gt;
&lt;OrderLineItems&gt;
&lt;OrderLineItem&gt;
&lt;ArticleId&gt;250&lt;/ArticleId&gt;
&lt;Quantity&gt;3&lt;/Quantity&gt;
&lt;Price&gt;9.99&lt;/Price&gt;
&lt;/OrderLineItem&gt;
&lt;/OrderLineItems&gt;
&lt;/Order&gt;'


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 (
'&lt;Order OrderId="42" OrderTotal="49.96"&gt;
&lt;Customer Id="123"/&gt;
&lt;OrderLineItems&gt;
&lt;OrderLineItem&gt;
&lt;ArticleId&gt;250&lt;/ArticleId&gt;
&lt;Quantity&gt;3&lt;/Quantity&gt;
&lt;Price&gt;9.99&lt;/Price&gt;
&lt;/OrderLineItem&gt;
&lt;/OrderLineItems&gt;
&lt;/Order&gt;');

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