You need to use XQuery.
Side note: A great resource for several XQuery solutions is Jacob Sebastians blog
DECLARE @tbl TABLE
(
id INT,
col XML
)
INSERT INTO @tbl
SELECT 1,'<Product>
<name>car</name>
<color>red</color>
<type>sedan</type>
</Product>' UNION ALL
SELECT 2,'<Product>
<name>car2</name>
<color>blue</color>
<type>rabbit</type>
</Product>'
SELECT
id,
T.c.value('name[1]','VARCHAR(30)') AS name,
T.c.value('color[1]','VARCHAR(30)') AS color,
T.c.value('type[1]','VARCHAR(30)') AS TYPE
FROM @tbl tbl
CROSS APPLY col.nodes('Product')T(c)
WHERE id=2
Lutz
A pessimist is an optimist with experience. How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]