从XML的所有路径获取键值

Question

我有一个xml

<EmployeeDetails>
<BusinessEntityID>3</BusinessEntityID>
<StoreDetail>
<Sale>
<AnnualSales>800000</AnnualSales>
</Sale>
<Sale>
<AnnualSales>300000</AnnualSales>
</Sale>
</StoreDetail>
<Revenue>
<StoreDetail>
<Sale>
<AnnualSales>800000</AnnualSales>
</Sale>
<Sale>
<AnnualSales>300000</AnnualSales>
</Sale>
</StoreDetail>
</Revenue>
<Report>
<Sale>
<AnnualSales>900000</AnnualSales>
</Sale>
</Report>
</EmployeeDetails>

我想从节点<sale>中找到AnnualSales的所有值。它可以在EmployeeDetails-->StoreDetail-->Sale或EmployeeDetails-->StoreDetail-->Revenue-->Sale下，也可以在其他树下。是否有可能找到所有sale不管路径?

目前我使用下面的代码，我只得到一个路径(2行)

SELECT 
X.Y.value('(AnnualSales)[1]', 'VARCHAR(20)') as AnnualSales
FROM test e
CROSS APPLY e.xml_data.nodes('EmployeeDetails/StoreDetail/Sale') as X(Y)

DbFiddle

预期输出:

800000
300000
800000
300000
900000

Answer 1

请尝试以下操作

由于XML结构不一致，我们必须在.nodes()方法中使用效率最低的XPath表达式。而且我不确定返回值的正确数据类型。恕我直言，可能是DECIMAL(x,y)。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<EmployeeDetails>
<BusinessEntityID>3</BusinessEntityID>
<StoreDetail>
<Sale>
<AnnualSales>800000</AnnualSales>
</Sale>
<Sale>
<AnnualSales>300000</AnnualSales>
</Sale>
</StoreDetail>
<Revenue>
<StoreDetail>
<Sale>
<AnnualSales>800000</AnnualSales>
</Sale>
<Sale>
<AnnualSales>300000</AnnualSales>
</Sale>
</StoreDetail>
</Revenue>
<Report>
<Sale>
<AnnualSales>900000</AnnualSales>
</Sale>
</Report>
</EmployeeDetails>');
-- DDL and sample data population, end
SELECT c.value('(./text())[1]', 'VARCHAR(20)') AS AnnualSales
FROM @tbl CROSS APPLY xmldata.nodes('//AnnualSales') AS t(c);

+-------------+
| AnnualSales |
+-------------+
|      800000 |
|      300000 |
|      800000 |
|      300000 |
|      900000 |
+-------------+