declare @var xml set @var='<ProductDescription ProductModelID="500">123</ProductDescription> <ProductDescription ProductModelID="600">123</ProductDescription>' select @var.value('(//ProductDescription/@ProductModelID)[1]','int') as id1, @var.value('(//ProductDescription/@ProductModelID)[2]','int') as id2 id1 id2 500 600
set @var='<ProductDescription ProductModelID="500">123</ProductDescription>
<ProductDescription ProductModelID="600">123</ProductDescription>'
select @var.value('(//ProductDescription/@ProductModelID)[1]','int') as id1,
@var.value('(//ProductDescription/@ProductModelID)[2]','int') as id2
id1 id2
500 600