declare @xmltable table
(
x xml
)

insert @xmltable
select '<Product><Product_ID>Q001</Product_ID></Product><Product><Product_ID>Q002</Product_ID></Product>'

SELECT m.n.value('Product_ID[1]','varchar(10)') AS ProductID
FROM
(SELECT CAST('<Products>' + cast(x AS varchar(max)) + '</Products>' AS xml) AS xmlCol FROM @xmltable)t
CROSS APPLY xmlCol.nodes('/Products/Product')m(n)


output
--------------------------------------
ProductID
--------------------------------------
Q001
Q002