Append namespace in XSL transformation

You're making life much too complicated here and you're getting some things wrong.

<xsl:template match="my_value">
    <xsl:element name="my_node">
        <xsl:namespace name="tns" select="'http://www.example.com'"/>
        <xsl:element name="my_child">
            <xsl:value-of select="my_value"/>
        </xsl:element>
    </xsl:element>
</xsl:template>

Firstly, xsl:namespace is only needed if you want to inject a namespace that isn't used in any element or attribute names. When you're creating elements and attributes you need to say what namespace they're in, and the namespace nodes/declarations will then be created automatically.

Secondly, the context node for the template is the my_value element so you get its value using ., not using my_value. Context is everything.

So you could write:

<xsl:template match="*">
    <xsl:element name="tns:my_node" namespace="http://www.example.com"/>
        <xsl:element name="my_child" namespace="http://www.example.com">
            <xsl:value-of select="."/>
        </xsl:element>
    </xsl:element>
</xsl:template>

But xsl:element is needed only when the name of the element you're constructing isn't known statically. When the name is fixed, it's much better to use a literal result element:

<xsl:template match="*">
    <tns:my_node xmlns:tns="http://www.example.com"/>
        <tns:my_child>
            <xsl:value-of select="."/>
        </tns:my_child>
    </tns:my_node>
</xsl:template>

Finally with XSLT 3.0 if you set the expand-text="yes" option you can simplify this further to:

<xsl:template match="*">
    <tns:my_node xmlns:tns="http://www.example.com"/>
        <tns:my_child>{.}</tns:my_child>
    </tns:my_node>
</xsl:template>

A simple use of literal result elements should suffice:

<xsl:template match="my_value">
  <tns:my_node xmlns:tns="http://www.example.com">
    <tns:my_child>
      <xsl:value-of select="."/>
    </tns:my_child>
  </tns:my_node>
</xsl:template>

As you seem to be using XSLT 3 you could also use a text value template instead of xsl:value-of:

<xsl:template match="my_value" expand-text="yes">
  <tns:my_node xmlns:tns="http://www.example.com">
    <tns:my_child>{.}</tns:my_child>
  </tns:my_node>
</xsl:template>

Not sure I got the whole intent, but could you try this:

<xsl:stylesheet xmlns:tns="http://www.example.com" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:math="http://www.w3.org/2005/xpath-functions/math" xmlns:array="http://www.w3.org/2005/xpath-functions/array" xmlns:map="http://www.w3.org/2005/xpath-functions/map" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:err="http://www.w3.org/2005/xqt-errors" exclude-result-prefixes="array fn map math xhtml xs err" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3.0">
    
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    
    <xsl:template match="*">
        <xsl:element name="tns:my_node">
            <xsl:element name="tns:my_child">
                <xsl:value-of select="."/>
            </xsl:element>
        </xsl:element>
    </xsl:template>
  
</xsl:stylesheet>

So, we did the following amends:

1. Introduced xmlns:tns="http://www.example.com" attribute to the xsl:stylesheet root element instead of dedicated xsl:namespace tag
2. Applied the namespace prefix to my_node and my_child right in place (now it's legit because we introduced the prefix)
3. Did a selection of "." instead of my_value

As a result, we have:

<tns:my_node xmlns:tns="http://www.example.com">
   <tns:my_child>test123</tns:my_child>
</tns:my_node>