Query XML String as a SQL Table.

This basic sample demonstrates how to read string xml data as a normal sql table.
This is very useful is you want to parse bulk data into sql server stored procedure.
For this example I am using ArrayList of my “Test” class. And using XmlSerializer I am going to convert it in to XML. For demonstration purpose I am just declare a SQL variable “@XMLString” to hold xml data.

declare @XMLString xml

set @XMLString = '?<ArrayOfTest>
<Test>
<TestID>1</TestID >
<TestName>ASP.NET Test</TestName>
<TestDuration>2 Hrs</TestDuration>
</Test>
<Test>
<TestID>2</TestID>
<TestName>SharePoint Test</TestName>
<TestDuration>2 Hrs</TestDuration>
</Test>
<Test>
<TestID>3</TestID>
<TestName>SQL Server Test</TestName>
<TestDuration>2 Hrs</TestDuration>
</Test>
</ArrayOfTest>'

Now we can perform query against @XMLString using nodes function and xpath.

select TableAlias.ColumnAlias.value('TestID[1]','VARCHAR(20)') ,
TableAlias.ColumnAlias.value('TestName[1]','VARCHAR(20)') ,
TableAlias.ColumnAlias.value('TestDuration[1]','VARCHAR(20)')

from @XMLString.nodes('//ArrayOfTest/Test')TableAlias(ColumnAlias)

Example 2:
Here is an example of little advanced filtering with XPath.

select TableAlias.ColumnAlias.value('TestID[1]','VARCHAR(20)') ,
TableAlias.ColumnAlias.value('TestName[1]','VARCHAR(20)') ,
TableAlias.ColumnAlias.value('TestDuration[1]','VARCHAR(20)')
from @XMLString.nodes('//ArrayOfTest/Test[TestID >= 2]')TableAlias(ColumnAlias)

Download Source
Click here to Read MSDN for more info