sql-server - TSQL - XML 查询帮助

Question

我有一个这种格式的 XML

<tests>
    <test>
        <testid>1</testid>
        <testval>8</testval>
        <testname>
            <testid>1</testid>
            <testname>test 1</testname>
        </testname>
    </test>
    <test>
        <testid>2</testid>
        <testval>5</testval>
        <testname>
            <testid>2</testid>
            <testname>test 2</testname>
        </testname>
    </test>
</tests>

使用 TSQL/XML 查询我如何获得这个结果

[Testid][TestVal][TestName]
    1       8      Test 1
    2       5      Test 2

Answer 1

试试这个:

declare @input XML = '<tests>
    <test>
        <testid>1</testid>
        <testval>8</testval>
        <testname>
            <testid>1</testid>
            <testname>test 1</testname>
        </testname>
    </test>
    <test>
        <testid>2</testid>
        <testval>5</testval>
        <testname>
            <testid>2</testid>
            <testname>test 2</testname>
        </testname>
    </test>
</tests>'

select
    Tests.value('(testid)[1]', 'int') as 'TestID',
    Tests.value('(testval)[1]', 'int') as 'TestVal',
    Tests.value('(testname/testname)[1]', 'varchar(20)') as 'TestName'
FROM 
    @input.nodes('/tests/test') as List(Tests)

这会为您提供所需的输出。

如果您有一个包含这些 XML 列的表，您可能需要使用稍微不同的方法(使用 CROSS APPLY):

select
    tbl.SomeValue, tbl.SomeOtherValue,
    Tests.value('(testid)[1]', 'int') as 'TestID',
    Tests.value('(testval)[1]', 'int') as 'TestVal',
    Tests.value('(testname/testname)[1]', 'varchar(20)') as 'TestName'
FROM 
    dbo.YourTable tbl
CROSS APPLY
    tbl.XmlColumn.nodes('/tests/test') as List(Tests)