Copy-of to copy root and all children to an xml database column

Question

In BizTalk, I'm trying an XSLT with the pattern below. I have calling a SQL stored procedure, and there are some fields before and after the XML column, but the XML column needs to contain the entire XML from the source document.

I have tried two ways, shown below. The first one results in the root node being copied to the right place, but none of its children. I read earlier today that copy-of is deep-copy (whereas copy is a shallow copy), so not sure why it's not copying the children recursively.

Try 1:

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" 
version="1.0" 
xmlns:ns99="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/DCMS_BLD1" 
xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp">
    <xsl:output omit-xml-declaration="yes" method="xml" version="1.0"/>
    <xsl:template match="/">
        <xsl:element name="ns0:USP_INS_DIH_RECORD">

            <ns99:Field1>NealTest2</ns99:Field1>

            <ns99:Field2XmlColumn>
                <xsl:copy-of select="node() | @*"/>
            </ns99:Field2XmlColumn>

            <ns99:FieldCreatedDateTime>TODAY</ns99:FieldCreatedDateTime>

        </xsl:element>
    </xsl:template>
    

</xsl:stylesheet>

Try 2:

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" 
version="1.0" 
xmlns:ns99="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/DCMS_BLD1" >
    <xsl:output omit-xml-declaration="yes" method="xml" version="1.0"/>
    <xsl:template match="/">
        <xsl:element name="ns0:USP_INS_DIH_RECORD">
            <ns99:Field1>NealTest2</ns99:Field1>
            <ns99:Field2XmlColumn>
                <xsl:apply-templates select="*"/>
            </ns99:Field2XmlColumn>
            <ns99:FieldCreatedDateTime>Today</ns99:FieldCreatedDateTime>

        </xsl:element>
    </xsl:template>
    
    <xsl:template match="/*">
        <xsl:copy-of select="node() | @*"/>
    </xsl:template>
    

</xsl:stylesheet>

Someone in comment asked for sample input. The input shouldn't matter, since it is a direct copy, but here's a dummy example:

<ns0:dummy xmlns:ns0="http://myschema.dummy">
    <ns0:firstname>John</ns0:firstname>
    <ns0:lastname>Doe</ns0:lastname>
</ns0:dummy>

I removed the C#, and tried it outside of BizTalk on a site like this: https://www.online-toolz.com/tools/xslt-transformation.php# and it worked fine. But it didn't create a shareable link with my data/xsl.

I have also changed the namespace in the xslt to ns99 so it won't conflict with ns0 in the incoming data.

Answer 1

The problem had nothing to do with XSLT. I was testing with an input file that only had a root element.