T-SQL Statement

Question

DECLARE

@STRSQL XML

SET

@STRSQL=N'<records>

<rows serviceid="93" servicetypeid="2" />

<rows serviceid="62" servicetypeid="2" />

<rows serviceid="53" servicetypeid="1" />

<rows serviceid="93" servicetypeid="2" />

</records>'

 

How do i

get the duplicate service id ?

Answer 1

Couple of ways:

DECLARE @strsql XML 

SET @strsql=N'<records> 
<rows serviceid="93" servicetypeid="2" />
<rows serviceid="62" servicetypeid="2" />
<rows serviceid="53" servicetypeid="1" />
<rows serviceid="93" servicetypeid="2" />
</records>'
 
-- Traditional SQL method for find duplicates
SELECT serviceid, COUNT(*) AS records
FROM
	(
	SELECT 
		x.y.value('@serviceid', 'INT') AS serviceid,
		x.y.value('@servicetypeid', 'INT') AS servicetypeid
	FROM @strsql.nodes('records/rows') x(y)
	) x
GROUP BY serviceid
HAVING COUNT(*) > 1

-- XQuery method
SELECT @strsql.query('for $s in distinct-values(records/rows/@serviceid) 
	where count(records/rows[@serviceid=$s]) > 1
	return $s'
	).value('.', 'INT' )

On my laptop the traditional SQL method appeared faster but you should compare and contrast with your own data.