find all duplicate values in an element attribute

Question

I have an xml document that is stored in sql server 2005 that I want to find all duplicate values from an id attribute within an 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&lt;/AgencyListRequestURL&gt;
    <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 am looking to generate a sql result that looks in all the “ID” attributes in the entire XML document and will tell me that “ID-2” is a duplicate value.

Is this possible in XPATH?

Thanks for your help!

Answer 1

  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 the XPath //*[@id] that selects all document elements with an id attribute.

Update

If the 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 across all rows. If you want to select duplicate attribute values only within each row, add the table primary key to the group by:

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;