8
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?

8

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);
| improve this answer | |
  • Thanks, yes, I have an XSLT assembly I occasionally use from Microsoft Master Data Services that does XML transforms. – Cade Roux Sep 14 '19 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 '19 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 '19 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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