CODEWITHSUNDEEP
xmlsql-server-2008-r2
Jagadeesh Jupalli
Jagadeesh Jupalli

Reputation: 13

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

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.

Upvotes: 1

Views: 1619

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

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}');

Upvotes: 0

Il Vic
Il Vic

Reputation: 5666

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.

Upvotes: 1

Related Questions