TSQL - help with XML query

Question

I have XML in this format

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

using TSQL / XML query how to achieve this result

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

Answer 1

Try the following:

 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) 

This gives the desired result.

If you have a table of these XML columns, you might need to use a slightly different approach (using 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)