How to delete multiple nodes in XML using SQL

Question

I have a column name XMLData which contain large XML (around 10,000 lines). Below is the type of XML stored.

<ABC>
  <DEF>
    <GHI>
      <JKL>value1</JKL>
      ..
    </GHI>
    <GHI>
      <JKL>value2</JKL>
      ..
    </GHI>
    <GHI>
      <JKL>value3</JKL>
      ..
    </GHI>
    ..
    ..
    <GHI>
      <JKL>valueN</JKL>
      ..
    </GHI>
    <OtherNodes>
    <OtherNodes1>
    .
    .
    .
  </DEF>
</ABC>

Is there a way to delete multiple nodes in SQL query? Specifically, I want to delete all GHI nodes under the Node DEF. Thanks!!

It probably more efficient to export, row, modify, xml, the update row. If you are using SQL Server I recommend on of the command line executable like sqlcmd.exe or bcp.exe. See msdn.microsoft.com/en-us/library/ms162816.aspx — jdweng
– jdweng, Commented Jul 22, 2015 at 10:29
@jdweng - do you have a specific reason for avoiding the SQL Server xml data type functionality (I'm always happy to learn something new) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 22, 2015 at 10:40
It is easier to parse xml outside sql than inside. Outside you can use other languages not avaiable inside. You can put the call to the command language inside other programs using SHELL for example. — jdweng
– jdweng, Commented Jul 22, 2015 at 11:35

Damien_The_Unbeliever · Accepted Answer · 2015-07-22 10:33:53Z

2

For SQL Server, you'd just use modify with delete:

declare @x xml = '<ABC>
  <DEF>
    <GHI>
      <JKL>value1</JKL>
    </GHI>
    <GHI>
      <JKL>value2</JKL>
    </GHI>
    <GHI>
      <JKL>value3</JKL>
    </GHI>
    <GHI>
      <JKL>valueN</JKL>
    </GHI>
    <OtherNodes/>
    <OtherNodes1/>
  </DEF>
</ABC>'

set @x.modify('delete /ABC/DEF/GHI')

select @x

Result:

<ABC><DEF><OtherNodes /><OtherNodes1 /></DEF></ABC>

answered Jul 22, 2015 at 10:33

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

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

Collectives™ on Stack Overflow

How to delete multiple nodes in XML using SQL

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