--Creating a temporary table variable
declare @Temp table
(
XMLCol xml
)
--insert your xml data
insert into @Temp values
('<products>
<product>
<sku>1</sku>
<desc>Book</desc>
<test>pass</test>
</product>
</products>
')
insert into @Temp values
('<products>
<product>
<sku>2</sku>
<desc>DVD</desc>
<test>pass</test>
</product>
</products>
')
insert into @Temp values
('<products>
<product>
<sku>3</sku>
<desc>Video</desc>
<test>fail</test>
</product>
</products>
')
--selecting the records from the xml column
select X.N.value(N'(sku)[1]', 'nvarchar(max)') as _sku,
X.N.value(N'(desc)[1]', 'nvarchar(max)') as _desc,
X.N.value(N'(test)[1]', 'nvarchar(max)') as _test
from @Temp as T
cross apply T.XMLCol.nodes(N'/products/product') as X(N)