Using XQuery to filter out some children, but preserve parents SET NOCOUNT ON;
DECLARE @xml AS Xml = '&lt;a&gt;&lt;b&gt;bbb&lt;/b&gt;&lt;c&gt;ccc&lt;/c&gt;&lt;d&gt;ddd&lt;/d&gt;&lt;/a&gt;';
SELECT @xml;
SELECT @xml.query('/a/*[self::b or self::c]');
SET @xml.modify('delete /a/d');
SELECT @xml;


Gives the following resultsets

Original:

&lt;a&gt;&lt;b&gt;bbb&lt;/b&gt;&lt;c&gt;ccc&lt;/c&gt;&lt;d&gt;ddd&lt;/d&gt;&lt;/a&gt;


Filtered to exclude non-(b|c) - but parents are missing:

&lt;b&gt;bbb&lt;/b&gt;&lt;c&gt;ccc&lt;/c&gt;


What I want (available with delete steps but not with .query):

&lt;a&gt;&lt;b&gt;bbb&lt;/b&gt;&lt;c&gt;ccc&lt;/c&gt;&lt;/a&gt;


Is is possible to preserve the parents in an XQuery?
You may try following

SELECT @xml.query('
    element a {
        for $node in /a/*[local-name() != "d"]
            return $node
    }');


But it may not be suitable if your real XML is more complex and you have to exclude node at a deeper level of nesting.



Speaking honestly, I don't think that XQuery is the good tool for such sort of tasks. In essence we have XML and we need to remove a node somewhere within, i.e. we need to transform it. The more suitable tool, I think, is XSL transformations. SQL Server has no built-in XSLT capabilities unfortunately (it can be added via SQLCLR function, though).

This is how body of XSL for solving this task could look

&lt;!-- Skip "d" under "a" --&gt;
&lt;xsl:template match="a/d" /&gt;

&lt;!-- Apply identity transform to other nodes --&gt;
&lt;xsl:template match="@*|node()"&gt;
  &lt;xsl:copy&gt;
    &lt;xsl:apply-templates select="@*|node()" /&gt;
  &lt;/xsl:copy&gt;
&lt;/xsl:template&gt;


It will work for a element residing anywhere within XML, not just at the root.

SQLCLR-function for performing non-parameterized XSL transformation can be as simple as few lines of C# code

[SqlFunction(Name = "XslTransform")]
public static SqlXml XslTransform(SqlXml xml, SqlXml xsl)
{
    if (xml.IsNull || xsl.IsNull)
        return SqlXml.Null;

    var xslt = new XslCompiledTransform();
    using (var xr = xsl.CreateReader())
        xslt.Load(xr);

    var xws = new XmlWriterSettings
    {
        Encoding = Encoding.Unicode,
        OmitXmlDeclaration = true
    };

    var output = new MemoryStream();
    using (var xw = XmlWriter.Create(output, xws))
    using (var xr = xml.CreateReader())
    {
        xslt.Transform(xr, null, xw);
        xw.Flush();
    }

    output.Seek(0, SeekOrigin.Begin);

    return new SqlXml(output);
}


It should be declared in database like

CREATE FUNCTION SQLCLR.XslTransform
(
    @xml xml,
    @xsl xml
)
RETURNS xml
AS EXTERNAL NAME [AssemblyName].[ClassName].[XslTransform];
GO


And it can be used then as

DECLARE
    @xml xml = N'(Your XML goes here)',
    @xsl xml = N'(Your XSL goes here)';

SELECT SQLCLR.XslTransform(@xml, @xsl);