asked on

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

stalhw

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

Ken Butters

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...

Camillia

ASKER

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

Camillia

ASKER

Tried this but still gives me 9 rows of node 1 (3 and AA)

stalhw

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

stalhw

I didn't know sql could hanlde xml that way, thanks for the question, it made me learn something today :-)

ASKER CERTIFIED SOLUTION

stalhw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

let me try