input data

    <?xml version="1.0" encoding="UTF-8"?>
<root>
    <PETS>
        <cats_stylesheet><![CDATA[<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>
    <xsl:template match="/">
        <Cats>
            <name>
                <xsl:value-of select="'Felix'"/>
            </name>
            <breed>
                <xsl:value-of select="cat/breed"
            </breed>
            <age>
                <xsl:value-of select="cat/age"
            </age>
        </Cats>
    </xsl:template>
</xsl:stylesheet>
]]></cats_stylesheet>
        <dogs_stylesheet><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>
    <xsl:template match="/">
        <Dogs>
            <name>
                <xsl:value-of select="'Rover'"/>
            </name>
            <breed>
                <xsl:value-of select="dog/breed"/>
            </breed>
            <age>
                <xsl:value-of select="dog/age"/>
            </age>
        </Dogs>
    </xsl:template>
</xsl:stylesheet>
]]></dogs_stylesheet>
    </PETS>
</root>

Desired output

This should just be a copy of the original but with only the Dog name changed.

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <PETS>
        <cats_stylesheet><![CDATA[<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>
    <xsl:template match="/">
        <Cats>
            <name>
                <xsl:value-of select="'Felix'"/>
            </name>
            <breed>
                <xsl:value-of select="cat/breed"
            </breed>
            <age>
                <xsl:value-of select="cat/age"
            </age>
        </Cats>
    </xsl:template>
</xsl:stylesheet>
]]></cats_stylesheet>
        <dogs_stylesheet><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>
    <xsl:template match="/">
        <Dogs>
            <name>
                <xsl:value-of select="'Cerberus'"/>
            </name>
            <breed>
                <xsl:value-of select="dog/breed"/>
            </breed>
            <age>
                <xsl:value-of select="dog/age"/>
            </age>
        </Dogs>
    </xsl:template>
</xsl:stylesheet>
]]></dogs_stylesheet>
    </PETS>
</root>

what I tried

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:axsl="http://www.w3.org/1999/XSL/TransformAlias"
    version="3.0">
    <xsl:output method="xml" encoding="UTF-8" indent="yes" 
        cdata-section-elements="cats_stylesheet dogs_stylesheet" 
        exclude-result-prefixes="xsl"/>
    <xsl:namespace-alias stylesheet-prefix="axsl" result-prefix="xsl"/>

    <xsl:template match="@* | node()">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()"/>
        </xsl:copy>
    </xsl:template>
    
    <xsl:template match="dogs_stylesheet">

        <xsl:variable name="contents_of_cdata">
            <xsl:value-of disable-output-escaping="true" select="text()"/>
        </xsl:variable>
    
        <dogs_stylesheet>
            <Dogs>
                <name>
                    <xsl:value-of select="'Cerberus'"/>
                </name>
                <xsl:copy-of select="$contents_of_cdata/xsl:stylesheet/xsl:template/Dogs/node()[not(self::Dogs/name)]"/>
            </Dogs>
        </dogs_stylesheet>

    </xsl:template>
</xsl:stylesheet>

I tried to convert the CDATA text into a 'nodeset' (to use the old xsltl 1.0 term), but that's not working.

1. it's not putting dogs_stylesheet in a CDATA tag
2. it's inserting the namespace dogs_stylesheet
3. the CDATA text wasn't converted into a 'nodeset'

How can I achieve the desired output?

It seems it suffices to use parse-xml, then transform the result, changing that attribute in a template (the rest is handled by the identity transformation), then to serialize back and ensure the outer element is serialized as a CDATA section:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="#all"
    version="3.0">
  
  <xsl:template match="dogs_stylesheet">
    <xsl:copy>
      <xsl:variable name="transformed-stylesheet">
        <xsl:apply-templates select="parse-xml(.)"/>
      </xsl:variable>
      <xsl:value-of select="serialize($transformed-stylesheet)"/>
    </xsl:copy>
  </xsl:template>

  <xsl:mode on-no-match="shallow-copy"/>
  
  <xsl:template match="xsl:stylesheet/xsl:template/Dogs/name/xsl:value-of/@select">
     <xsl:attribute name="{name()}" select="'''Cerberus'''"/>
  </xsl:template>

  <xsl:output method="xml" indent="yes" cdata-section-elements="cats_stylesheet dogs_stylesheet"/>
  
</xsl:stylesheet>