Select (XML) brings back duplicate rows

I have this stored poc and I'm not sure why it's bringing 9 rows back. I want it to being back 3 rows. Am I not doing the XMl
correctly? I want the XML I'm passing to have 2 fields and 3 rows and bring back those fields. If you run it, it bring back
9 rows.

3  AA
6  BB
15 CC

Stored proc

ALTER PROCEDURE [dbo].[APIXMLTest](@productIds xml)
 AS 
 
 BEGIN
DECLARE @Products TABLE (ID INT, ID2 VARCHAR(2))

 INSERT INTO @Products (ID, ID2)
  SELECT  ParamValues.ID.value('.','VARCHAR(20)'),  ParamValues2.ID.value('.','VARCHAR(20)')
   FROM @productIds.nodes('/Products/idSet/id') as ParamValues(ID) , @productIds.nodes('/Products/idSet/id2') as ParamValues2(ID)
  
  SELECT * FROM @Products

END	

---

EXEC   APIXMLTest
 @productIds='<Products><idSet><id>3</id><id2>AA</id2></idSet><idSet><id>6</id><id2>BB</id2></idSet><idSet><id>15</id><id2>CC</id2></idSet></Products>' 

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
 
stalhwConnect With a Mentor Commented:
Oh, checking again, it seems the "data(" is not really usefull...
this simpler version also works:
INSERT INTO @Products (ID, ID2)
SELECT  col.value('id[1]','varchar(20)') as ID, 
col.value('id2[1]','varchar(20)') as ID2
FROM @productIds.nodes('/Products/idSet') tbl(col)

Open in new window

0
 
stalhwCommented:
I'm not familiar with inserting from XML, but one thing is obvious to me.

FROM @productIds.nodes('/Products/idSet/id') as ParamValues(ID) , @productIds.nodes('/Products/idSet/id2') as ParamValues2(ID)
 
You are inserting from a query that selects from 2 tables, not joined...
That means for each rows of table1 (id) it will join every row of table2 (id2)
So the problem is there, now , how can we fix that, i'm not sure
0
 
Ken ButtersCommented:
In your from clause... you are choosing 3 values for "id" and 3 values for "id2"... and therefore returning every combination of the 3 x 3....

I think you need to change your "from" clause so that you are getting values from idSet...

and change your select so that you select IdSet/ID and IdSet/ID2...
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
CamilliaAuthor Commented:
how , been playing aound with it and not sure how to do it... found this but still not close

http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty
0
 
CamilliaAuthor Commented:
Tried this but still gives me 9 rows of node 1 (3 and AA)
0
 
stalhwCommented:
Ok, I did some research...
The following should work.
INSERT INTO @Products (ID, ID2)
SELECT  col.value('data(id[1])','varchar(20)') as ID, 
col.value('data(id2[1])','varchar(20)') as ID2
FROM @productIds.nodes('/Products/idSet') tbl(col)

Open in new window

0
 
stalhwCommented:
I didn't know sql could hanlde xml that way, thanks for the question, it made me learn something today :-)
0
 
CamilliaAuthor Commented:
let me try
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.