Using XQuery to filter out some children, but preserve parents

SET NOCOUNT ON;
DECLARE @xml AS Xml = 'bbbcccddd';
SELECT @xml;
SELECT @xml.query('/a/*[self::b or self::c]');
SET @xml.modify('delete /a/d');
SELECT @xml;

Gives the following resultsets

Original:

bbbcccddd

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

bbbccc

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

bbbccc

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









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


StackExchange Database Administrators Q#248763, answer score: 9