Find all duplicate values in an attribute of an element
I have an XML document that is stored in SQL Server 2005 and I want to find all duplicate values from the id attribute in the xml element. The xml looks like this:
<?xml version="1.0" encoding="utf-8"?>
<session sessionValue="" id="ID-1">
<data id="ID-3">
<id>d394E6FF844734CB9A5E8B17612DC050A</id>
<TotalResult>803</TotalResult>
<AgencyListRequestURL>http://ews.harleysvillegroup.com:5555/invoke/HmiPortalDCTRedirect.flows:getAgencyList</AgencyListRequestURL>
<system id="systemid">
<global id="ID-2">
<id>gEBE0E6C2D61340698B264E30D1B2DC59</id>
<Button />
<GlobalOutputSpacer />
<Spacer />
<LocationIDToCompare />
<MasterManuScriptID />
<CurrentVehicle />
</global>
<page id="ID-2">
<id>p7B81433D8EB4400CA775CB3F7F0AD4DE</id>
<DialogMode>0</DialogMode>
<DifferentAddress>0</DifferentAddress>
</page>
</system>
</data>
</session>
In this example, I want to create a SQL result that will look in all "ID" attributes throughout the XML document and tell me that "ID-2" is a duplicate value.
Is this possible in XPATH?
Thank you for your help!
source to share
select id, count(*)
from (
select x.value('@id', 'varchar(100)') as id
from @x.nodes('//*[@id]') t(x)) as ids
group by id
having count(*) > 1;
The key is an XPath //*[@id]
that selects all elements of the document with the attribute id
.
Update
If XML is in a table field:
select id, count(*)
from (
select x.value('@id', 'varchar(100)') as id
from [<table>]
cross apply [<table>].[<field>].nodes('//*[@id]') t(x)) as ids
group by id
having count(*) > 1;
This will select duplicate values for all rows. If you only want to select the duplicate attribute values in each row, add the table's primary key to the group:
select id, <primary key>, count(*)
from (
select x.value('@id', 'varchar(100)') as id
, <primary key>
from [<table>]
cross apply [<table>].[<field>].nodes('//*[@id]') t(x)) as ids
group by id, <primary key>
having count(*) > 1;
source to share