How to remove xml nodes without attribute in Sql Server

Question

What is the correct way to remove all child nodes of the root node with a missing attribute from an xml field in Sql Server 2008?

My Xml looks like this, I want to remove all the child nodes of <root> that don't have the ln attribute specified

<root>
  <title />
  <title />
  <questionphrase ln="nl">
    <xhtml />
  </questionphrase>
  <questionphrase ln="en">
    <xhtml />
  </questionphrase>
  <information ln="nl">
    <xhtml />
  </information>
  <information ln="en">
    <xhtml />
  </information>
  <title />
  <title ln="en">
     value
  </title>
  <label ln="en">
     value
  </label>
  <title />
  <title />
</root>

After the delete the xml should look like this

<root>
  <questionphrase ln="nl">
    <xhtml />
  </questionphrase>
  <questionphrase ln="en">
    <xhtml />
  </questionphrase>
  <information ln="nl">
    <xhtml />
  </information>
  <information ln="en">
    <xhtml />
  </information>
  <title ln="en">
     value
  </title>
  <label ln="en">
     value
  </label>
</root>

Are you wanting to permanently remove them in the table, or just hide them during a SELECT? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 29, 2013 at 9:51
I want to completely remove them, it's corrupt data that only causes overhead. — Mark
– Mark, Commented Jan 29, 2013 at 9:52

Hamlet Hakobyan · Accepted Answer · 2013-01-29 09:58:41Z

5

Try this:

DECLARE @xml XML = '....'
SET @xml.modify('delete //root/*[not(@ln)]')

SQL FIDDLE DEMO

answered Jan 29, 2013 at 9:58

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

How to remove xml nodes without attribute in Sql Server

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