CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-delete
Mark
Mark

Reputation: 3271

How to remove xml nodes without attribute in Sql Server

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>

Upvotes: 2

Views: 2378

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

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

SQL FIDDLE DEMO

Upvotes: 5

Related Questions