我想为该图表下所有筛选器值的第一列选择“图表名称”的XML层次结构值。
我使用了下面的方法,但是ChartName返回NULL。我怀疑我需要做一个子查询
DECLARE @input XML =
'<Report>
<DataSets>
</DataSets>
<ReportSections>
<ReportSection>
<ReportItems>
<Chart Name="Hub1">
<Filters>
<Filter>
<Expression>Fields!Hub.Value</Expression>
<Operator>Like</Operator>
<Values>
<Value>Central</Value>
</Values>
</Filter>
<Filter>
<Expression>Fields!ADP_Hrs.Value</Expression>
<Operator>NotEqual</Operator>
<Values>
<Value DataType="Float">0</Value>
</Values>
</Filter>
<Filter>
<Expression>Fields!TL.Value</Expression>
<Operator>Equal</Operator>
<Values>
<Value DataType="Integer">1</Value>
</Values>
</Filter>
</Filters>
</Chart>
</ReportItems>
</ReportSection>
</ReportSections>
</Report>'
SELECT
[Tbl].[Col].value('Chart [4]', 'varchar(50)') as ChartName,
[Tbl].[Col].value('Expression [1]', 'varchar(50)') as Expression,
[Tbl].[Col].value(' Operator [1]', 'varchar(50)') as 'Operator',
[Tbl].[Col].value(' Values [1]', 'varchar(50)') as 'Value'
FROM
@input.nodes('Report/ReportSections/ReportSection/ReportItems/Chart/Filters/Filter') as [Tbl]([Col])
预期产量:
ChartName Expression Operator Value
------------------------------------------------------
Hub1 Fields!Hub.Value Like Central
Hub1 Fields!ADP_Hrs.Value NotEqual 0
Hub1 Fields!TL.Value Equal 1
Chart
不在Filter
元素内,因此除非找到XML路径,然后拉出元素的@Name
属性,否则它将无法找到它Chart
:
SELECT
[Tbl].[Col].value('(../../@Name)[1]', 'varchar(50)') as ChartName,
[Tbl].[Col].value('Expression [1]', 'varchar(50)') as Expression,
[Tbl].[Col].value(' Operator [1]', 'varchar(50)') as 'Operator',
[Tbl].[Col].value(' Values [1]', 'varchar(50)') as 'Value'
FROM @input.nodes('Report/ReportSections/ReportSection/ReportItems/Chart/Filters/Filter') as [Tbl]([Col])
哪个返回:
ChartName Expression Operator Value
-------------- ----------------------- ------------ -------------
Hub1 Fields!Hub.Value Like Central
Hub1 Fields!ADP_Hrs.Value NotEqual 0
Hub1 Fields!TL.Value Equal 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句