Como essa tabela derivada com aplicação cruzada funciona?

O que você está vendo é a implementação do XQuery no SQL Server. Embora o XQuery use seu próprio analisador e execute sua própria algebrização durante o estágio de compilação da consulta, os resultados são combinados e otimizados junto com a parte DML da consulta e, em seguida, combinados em um único plano de execução.

O SQL Server oferece suporte a cinco métodos diferentes. value , exist , query e nodes são usados ​​para acessar e transformar os dados. O último, modifique , usa XML DML para modificar os dados.

O método value() retorna um valor escalar da instância XML Digamos que você tenha o 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')

Daria a você o ID do primeiro cliente do primeiro pedido

No seu caso particular value('.') significa dar-me todos os valores do elemento fragmentado (sobre o qual falarei em breve). Agora, para tornar mais fácil, não 'mover' muito, você pode usar o nó de função que fragmenta o XML em dados relacionais. Ele retorna um conjunto de linhas com linhas representando os nós identificados pela expressão de caminho.

Exemplo:

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)

Ao usar o método nodes() com a coluna XML da tabela, você deve usar o operador APPLY.

Exemplo:

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)

Eu usei exemplos sobre valor e nó, porque você forneceu o código apenas com essas duas funções, se você quiser ler mais sobre isso, visite este

Espero que estes exemplos simples lhe dêem uma ideia de como consultar tipos xml

O Cross Apply está sendo usado em conjunto com a .nodes()chamada XQuery. Isso basicamente está obtendo todos os elementos <I></I>para os arrays XML construídos na XMLTaggedListinstrução CTE. A chamada externa .value()de XQuery então extrai esse valor e o converte em um VARCHAR(MAX), retornando um registro para cada código dentro de cada grupo.

Acho mais fácil digerir se eu quebrar a consulta. Talvez você também.

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)

Um adendo à resposta de John Eisbrener :

Quanto à ExtractedDiagnosisList(X)peça, pode ajudar alguns de nós se houver um espaço nela:

ExtractedDiagnosisList (X)

A partir dos documentos SQL , tabelas derivadas (e funções de conjunto de linhas e @variable. function_calls) permitem que você especifique não apenas um alias de tabela, mas uma lista de aliases de coluna para as colunas retornadas na "tabela" em questão. Neste caso, há uma coluna, e vamos chamá-la de X.

Ver?

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

...

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

...

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

...

| @variable.function_call (expressão [ ,...n ] )
[ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]

...