Using XQuery to filter out some children, but preserve parents

Question

SET NOCOUNT ON;
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

Original:

<a><b>bbb</b><c>ccc</c><d>ddd</d></a>

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

<b>bbb</b><c>ccc</c>

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

<a><b>bbb</b><c>ccc</c></a>

Is is possible to preserve the parents in an XQuery?

i-one · Accepted Answer · 2019-09-14T13:25:11.473

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:copy>
    <xsl:apply-templates select="@*|node()" />
  </xsl:copy>
</xsl:template>

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

Using XQuery to filter out some children, but preserve parents

1 Answers1