SQL Server-从多个级别导入XML字段

戴夫

我想为该图表下所有筛选器值的第一列选择“图表名称”的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
詹姆斯·L

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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章