主页 / dba / 问题 / 177532
Accepted
Anthony Genovese
Asked: 2017-06-29 10:44:39 +0800 CST

这个带有交叉应用的派生表如何工作？

  • 772

我借用了一些关于如何压缩和解压缩诊断代码字符串的代码，以便为我的数据仓库构建一个桥接表。它运作良好。但是，我只是不明白它是如何进行压缩的。这是以下代码的 SQL Fiddle

create table dimDiagnosisGroup (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')


 WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('<I>' + REPLACE(dxCodeList, ', ', '</I><I>') + '</I>' AS XML)
            AS Diagnosis_Code_List
     FROM dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)

我理解XMLTaggedList部分很好。我不明白的是 Cross Apply 到ExtractedDiagnosisList(X)，然后是ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)')。

当我在 select 语句中将鼠标悬停在ExtractedDiagnosisList上时，SSMS 说它是一个派生表。但是，它对我来说有点像一个功能。我不明白Diagnosis_Code.List如何获取 .nodes('//I') 函数。最后，在 SQL 中， ExtractedDiagnosisList.X.value部分对我来说看起来很陌生。它看起来像 C# 等语言的语法。

sql-server functions

3 个回答

  1. Best Answer

    您看到的是 SQL Server 中的 XQuery 实现。尽管 XQuery 使用自己的解析器并在查询编译阶段执行自己的代数化，但结果会与查询的 DML 部分一起组合和优化，然后组合成一个执行计划。

    SQL Server 支持五种不同的方法。 value、exists、query和 nodes用于访问和转换数据。最后一个，modify，使用 XML DML 来修改数据。

    value() 方法从 XML 实例返回一个标量值假设您有 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')

    会给你第一个订单的第一个客户的 ID

    在您的特定情况下，value('.') 意味着给我来自切碎元素的所有值（我稍后会谈到）请记住，在使用值函数时，您正在通过 XML 移动。现在为了使它更容易，不要“移动”太多，您可以使用功能节点将 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 C.t.value('(@Id)[1]','int')
from  @X.nodes('/Order/Customer') as C(t)

    当您对表中的 XML 列使用 nodes() 方法时，您必须使用 APPLY 运算符。

    例子：

    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)

    我在值和节点上使用了示例，因为您仅提供了具有这两个功能的代码，如果您想了解更多信息，请访问此

    希望这个简单的例子能让你了解如何查询 xml 类型

    • 7

  2. Cross Apply 与.nodes()XQuery 调用结合使用。这基本上是获取在CTE 语句中<I></I>构造的 XML 数组的每个元素。XMLTaggedList然后，外部.value()XQuery 调用提取该值并将其强制转换为 a VARCHAR(MAX)，为每个组中的每个代码返回一条记录。

    如果我分解查询，我发现更容易消化。也许你也会。

    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)
    • 3

  3. John Eisbrener 回答的附录：

    就ExtractedDiagnosisList(X)部分而言，如果其中有空格，它可能会对我们中的一些人有所帮助：

    ExtractedDiagnosisList (X)

    从SQL 文档中，派生表（和行集函数，以及 @variable.function_calls）不仅允许您指定表别名，还允许您指定相关“表”中返回的列的列别名列表。在本例中，只有一列，我们将其命名为X。

    看？

    [ 来自 { } [ ,...n ] ]
    ::=
    {

    ...

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

    ...

    | 派生表 [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]

    ...

    | @variable.function_call ( 表达式 [ ,...n ] )
    [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]

    ...

    • 2

相关问题