TSQL - XML查询帮助

Question

我使用TSQL/XML查询我该如何实现这一结果有一个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> 

[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)