SQL Server: How to identify and delete duplicate XML data with specific attribute

Question

I have an XML column with some duplicate nodes (attributes) in it. I want to identify and delete them.

My XML looks like this.

<contact id="1">
  <lname>AA</lname>
  <fname>BB</fname>
</contact>
<contact id="2">
  <lname>CC</lname>
  <fname>DD</fname>
</contact>
**<contact id="2">
  <lname>EE</lname>
  <fname>FF</fname>
</contact>**
<contact id="3">
  <lname />
  <fname />
</contact>

I want to delete this node with has a duplicate attribute value of id="2".

<contact id="2">
  <lname>EE</lname>
  <fname>FF</fname>
</contact>

Please help.

Could you give a slightly more detailed explanation of the setup you're using? — Voidpaw
– Voidpaw, Commented May 18, 2015 at 15:20
I want to write a query to delete the duplicate node in the xml so that my final xml should look like this. <contact id="1"> <lname>AA</lname> <fname>BB</fname> </contact> <contact id="2"> <lname>CC</lname> <fname>DD</fname> </contact> <contact id="3"> <lname /> <fname /> </contact> — Jagadeesh Jupalli
– Jagadeesh Jupalli, Commented May 18, 2015 at 15:22
As @Yahya suggested: always give us an idea of what you have tried already. While it may be tempting to have other people solve your problems, we are much more inclined to help people who have shown effort. — Voidpaw
– Voidpaw, Commented May 18, 2015 at 15:24
I achieved the first part of identifying the duplicates... I did not post it here since i thought my script may misguide you. Here is my Script. create table #Members1 (MemberID varchar(50), XMLCount int) INSERT INTO #Members1 (MemberID, XMLCount) SELECT memberid ,C.value('@id', 'int') as [Info] FROM tblmembers WITH (NOLOCK) CROSS APPLY tblmembers.ContactInfo.nodes('contact') as X(C) DELETE FROM [#Members1] WHERE dbo.#Members1.XMLCount IN (1,3) SELECT MemberID, count() count FROM [#Members1] WITH (NOLOCK) GROUP BY dbo.#Members1.MemberID HAVING COUNT() > 1 — Jagadeesh Jupalli
– Jagadeesh Jupalli, Commented May 18, 2015 at 15:58

Il Vic · Accepted Answer · 2015-05-18 16:07:04Z

1

You can try by decomposing your XML, distinct different Ids with Row_Number and then reassembling the xml.

I mean something like this:

Select  t.id    As '@id',
        t.fname As 'fname',
        t.lname As 'lname'
From (
    Select  x.value('@id[1]', 'varchar(30)')    As id,
            x.value('lname[1]', 'varchar(30)')  As lname,
            x.value('fname[1]', 'varchar(30)')  As fname,
            Row_Number() Over (Partition By  x.value('@id[1]', 'varchar(30)') Order By x.value('@id[1]', 'varchar(30)')) As r
    from @x.nodes('/contact') as t(x)
    ) As t
Where t.r = 1
For Xml Path('contact')

I hope it can help you.

answered May 18, 2015 at 16:07

Il Vic

5,6564 gold badges28 silver badges38 bronze badges

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

1 Comment

Jagadeesh Jupalli Over a year ago

Thanks Il-Vic, Your solution worked for my example... I will try to implement it in my solution. Thank you very much for the help.

Solomon Rutzky · Accepted Answer · 2015-05-18 16:24:41Z

0

You should be able to do in-place modifications via XML Data Modification Language (XML DML), specifically the delete command. XML DML uses the modify() Method of the XML data type, along with an XQuery expression.

It should look similar to the following:

UPDATE SchemaName.TableName
SET XmlFieldName.modify('delete {XQuery expression}');

answered May 18, 2015 at 16:24

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Collectives™ on Stack Overflow

SQL Server: How to identify and delete duplicate XML data with specific attribute

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related