2

Let's say we have this simple XML structure:

<myXML>
    <data>
        <column value="1" />
        <column value="2" />
        <column value="3" />
        <column value="4" />
        <column value="5" />
        <column value="6" />
        <column value="7" />
        <column value="8" />
        <column value="9" />
        <column value="10" />
    </data>
    <data>
        <column value="1" />
        <column value="2" />
        <column value="3" />
        <column value="4" />
        <column value="5" />
        <column value="6" />
    </data>
    <data>
        <column value="3" />
        <column value="4" />
        <column value="5" />
        <column value="6" />
        <column value="7" />
    </data>
</myXML>

I need to remove all nodes from a specific list of values, for example (1,4,6,8,9), by using the XML modify statement.

Right now I'm using the following statement (it is build dynamically):

myXML.modify('delete for $Node in /data/column where ($Node/[@value=1]) or ($Node/[@value=3]) or ($Node/[@value=4]) return $Node')

But when I have big list of values it slows down the performance of my query. Is there any other way to do this?

gotqn
  • 42,737
  • 46
  • 157
  • 243
Mincho Minchev
  • 222
  • 3
  • 13
  • How big is your XML file? how many individual nodes and how many children average are those nodes expected to have? I tend to lean toward shredding into a table. Doing your modifying in SQL and then putting it back into XML using FOR XML. – Matt Apr 02 '15 at 15:23
  • My XML can be from 10 to 1000 individual nodes. In my case those nodes can have 2 children max and in their children's there is no restriction of values( they can be 1 to 1000 or even more). At the time i make this delete my biggest XML is like 50 000 lines or so. – Mincho Minchev Apr 03 '15 at 07:32

1 Answers1

0

where $Node/value = (1, 2, 3, 9, 12)

will be true if value is 1, is 2, is 3, is 9 or is 12

barefootliam
  • 619
  • 3
  • 7