Exclude a grandchild from XML using XPath

user_7917785 0
Viewed 54

Trying to exclude a specific grandchild when reading an XML structure using SQL Server 2017.

I have an example structure as follows:

<root>
    <a id="parent">
        <b id = "child1">
            <c id = "grandchild1"/>
            <c id = "grandchild2"/>
        </b>
        <b id = "child2">
            <c id = "grandchild3"/>
            <c id = "grandchild4"/>
        </b>
    </a>
</root>

I can exclude a specific child using c[not(contains(@id,"grandchild3"))] when I navigate to for example the node <b id = "child2">.

But how would I exclude that specific grandchild when processing the rest of the XML from the root node?

What i would like to receive, so I can write it to a file, is a structure like this:

<root>
    <a id="parent">
        <b id = "child1">
            <c id = "grandchild1"/>
            <c id = "grandchild2"/>
        </b>
        <b id = "child2">
            <c id = "grandchild4"/>
        </b>
    </a>
</root>

As can be seen grandchild3 is now missing from this structure.

xpath sql-server
1 Answers

Doing this in a SELECT is surprisingly involved, but fortunately you don't need that. You can make a copy of your xml, modify it with .modify, then use that:

declare @original xml = '<root>
    <a id="parent">
        <b id = "child1">
            <c id = "grandchild1"/>
            <c id = "grandchild2"/>
        </b>
        <b id = "child2">
            <c id = "grandchild3"/>
            <c id = "grandchild4"/>
        </b>
    </a>
</root>'

declare @new xml = @original

set @new.modify('delete //c[@id="grandchild3"]')

select @new

gives

<root>
  <a id="parent">
    <b id="child1">
      <c id="grandchild1" />
      <c id="grandchild2" />
    </b>
    <b id="child2">
      <c id="grandchild4" />
    </b>
  </a>
</root>

as desired.

The xpath //c[@id="grandchild3"] means "Any c node (anywhere in the document) with an id attribute with value grandchild3".

user_710590
Related