• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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

0
Camillia
Asked:
Camillia
  • 4
  • 3
1 Solution
 
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
stalhwCommented:
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
 
CamilliaAuthor Commented:
let me try
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now