Remove duplicate XML nodes SQL query

Question

I have the following XML.

<Root>
  <Child1>
    <SubChild1>some value</PartNumber>
  </Child1>
  <Child2>some data</Child2>
  <Child3>some data</Child3>
  <Child4>some data</Child4>
  <Child2>some data</Child2>
  <Child3>some data</Child3>
  <Child5>some data</Child5>
</Root>

I want to remove the duplicate nodes and return the XML in below format.

<Root>
  <Child1>
    <SubChild1>some value</PartNumber>
  </Child1>
  <Child2>some data</Child2>
  <Child3>some data</Child3>
  <Child4>some data</Child4>
  <Child5>some data</Child5>
</Root>

I tried below SQL query but it did not work.

SELECT @myXMLData.query('
    for $x in distinct-values(//*)[1]
        return
            (//*)[1]
')

I also tried distinct-values but it returns the values (without enclosing node attributes).

SELECT @XML.query('<Root>{ distinct-values(//*) }</Root>')

Any help with this please?

lptr · Accepted Answer · 2021-02-09 08:09:33Z

1

declare @x xml = N'<Root>
  <Child1>
    <SubChild1>some value</SubChild1>
  </Child1>
  <Child2>some data</Child2>
  <Child3>some data</Child3>
  <Child4>some data</Child4>
  <Child2>some data</Child2>
  <Child3>some data</Child3>
  <Child5>some data</Child5>
</Root>';

select node as '*'
from
(
select x.e.query('.') as node, row_number() over(partition by x.e.value('local-name(.)', 'nvarchar(100)') order by x.e) as nodenum
from @x.nodes('Root/*') as x(e)
) as src
where nodenum = 1
for xml path(''), root('Root');

answered Feb 9, 2021 at 8:09

lptr

6,8582 gold badges10 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Remove duplicate XML nodes SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related