sql-server - 查找元素属性中的所有重复值

Question

我有一个存储在 sql server 2005 中的 xml 文档，我想从 xml 元素中的 id 属性中查找所有重复值。 xml 看起来像这样:

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

在此示例中，我希望生成一个 sql 结果，该结果查看整个 XML 文档中的所有“ID”属性，并告诉我 “ID-2” 是重复值。

这在 XPATH 中可能吗？

谢谢你的帮助!

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;

关键是 XPath //*[@id]选择所有带有 id 的文档元素属性。

更新

如果 XML 在表字段中:

  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;

这将在所有行中选择重复值。如果只想在每一行中选择重复的属性值，请通过以下方式将表主键添加到组中:

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;