DECLARE @xml AS Xml = '<a><b>bbb</b><c>ccc</c><d>ddd</d></a>';
SELECT @xml;
SELECT @xml.query('/a/*[self::b or self::c]');
SET @xml.modify('delete /a/d');
SELECT @xml;

Gives the following resultsets



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


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


Is is possible to preserve the parents in an XQuery?

1 Answer 1


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

<!-- Skip "d" under "a" -->
<xsl:template match="a/d" />

<!-- Apply identity transform to other nodes -->
<xsl:template match="@*|node()">
    <xsl:apply-templates select="@*|node()" />

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())

    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);

    output.Seek(0, SeekOrigin.Begin);

    return new SqlXml(output);

It should be declared in database like

    @xml xml,
    @xsl xml
AS EXTERNAL NAME [AssemblyName].[ClassName].[XslTransform];

And it can be used then as

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

SELECT SQLCLR.XslTransform(@xml, @xsl);
  • Thanks, yes, I have an XSLT assembly I occasionally use from Microsoft Master Data Services that does XML transforms.
    – Cade Roux
    Sep 14, 2019 at 16:26
  • I checked it out and it works well, if they get more complex, I will definitely need to use a full XSLT.
    – Cade Roux
    Sep 14, 2019 at 17:33
  • @CadeRoux, yeah, it happens sometimes that use of .modify() and .query() is clunky or impossible, because of .modify() can only handle one node at a time and it turns that loops is not the option, because of it needs to be in SELECT, and it turns that XQuery is not the option too, because of its implementation in SQL Server is ancient and limited, etc etc ... in such cases XSLT may come into play with better elegance. But if simple solution using built-in capabilities is just enough, sure why not to use it.
    – i-one
    Sep 14, 2019 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.