Finding Duplicate Values in XML Column

Question

In my SQL Server DB I have table with an XML column. The XML that goes in it is like the sample below:

<Rows>
      <Row>
           <Name>John</Name>
      </Row>
      <Row>
           <Name>Debbie</Name>
      </Row>
      <Row>
           <Name>Annie</Name>
      </Row>
      <Row>
           <Name>John</Name>
      </Row>
</Rows>

I have a requirement that I need to find the occurrence of all rows where the XML data has duplicate entries of <Name>. For example, above we have 'John' twice in the XML.

I can use the exist XML statement to find 1 occurrence, but how can I find if it's more than 1? Thanks.

Tomalak · Accepted Answer · 2014-08-30 08:03:38Z

1

To identify any table row that has duplicate <Name> values in its XML, you can use exist as well:

exist('//Name[. = preceding::Name]')

To identify which names are duplicates, respectively, you need nodes and CROSS APPLY

SELECT
  t.id,
  x.Name.value('.', 'varchar(100)') AS DuplicateName
FROM
  MyTable t
  CROSS APPLY t.MyXmlColumn.nodes('//Name[. = preceding::Name]') AS x(Name)
WHERE
  t.MyXmlColumn.exist('//Name[. = preceding::Name]')

answered Aug 30, 2014 at 8:03

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shree.pat18 · Accepted Answer · 2014-08-30 08:03:45Z

1

Try this:

;with cte as
(SELECT tbl.col.value('.[1]', 'varchar(100)') as name
 FROM   yourtable
 CROSS APPLY xmlcol.nodes('/Rows/Row/Name') as tbl(col))

select name
from cte
group by name
having count(name) > 1

We first use the nodes function to convert from XML to relational data, then use value to get the text inside the Name node. We then put the result of the previous step into a CTE, and use a simple group by to get the value with multiple occurences.

Demo

answered Aug 30, 2014 at 8:03

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

1 Comment

shree.pat18 Over a year ago

@Tomalak Yeah it supports a lot of languages and even has autocomplete for C#. Very handy!

Collectives™ on Stack Overflow

Finding Duplicate Values in XML Column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related