Extracting xml from xml data type

[devans561]

Im trying to find a way to get these values from xml to a normalized table. The problem is that the xml should have a advertiser element whith child nodes id, prod..

How can I get all the data.? This query only brings back the first result off both result elements..

CREATE TABLE #xmld(rowid int IDENTITY(1,1), xdata xml)
DECLARE @xml xml
SET @xml='<search>
<result>
<adv_id>71</adv_id>
<prod_id>45</prod_id>
<paid>True</paid>
<adv_id>24</adv_id>
<prod_id>45</prod_id>
<paid>False</paid>
</result>
<result>
<adv_id>318</adv_id>
<prod_id>23</prod_id>
<paid>False</paid>
<adv_id>67</adv_id>
<prod_id>56</prod_id>
<paid>True</paid>
</result>
</search>'
INSERT INTO #xmld(xdata) VALUES (@xml)
SELECT 1 AS [Level],
xdata.value('(search/result/adv_id) [1]','integer') AS adv_id,
xdata.value('(search/result/prod_id) [1]','integer') AS prod_id,
xdata.value('(search/result/paid) [1]','bit') AS paid
FROM #xmld
UNION
SELECT 1 AS [Level],
xdata.value('(search/result/adv_id) [2]','integer') AS adv_id,
xdata.value('(search/result/prod_id) [2]','integer') AS prod_id,
xdata.value('(search/result/paid) [2]','bit') AS paid
FROM #xmld
DROP TABLE #xmld

[Island1]

Here's a thread that might at least point the way ...

http://www.sqlservercentral.com/Foru...345-338-1.aspx

Good Luck,

Bill