post by : A Technical Perspective
Requirement:
We need to call an SQL Server stored procedure based on parameters specified in an incoming XML message. In fact, the XML document contained a set of unbounded records that each needed to be associated with a call to a stored procedure.I could have used the debatching capabilities of the XML Disassembler pipeline component and implemented an orchestration implementing a scatter-gather like pattern, but since this solution was part of an ESB Toolkit 2.0 itinerary, I wanted to keep it as simple as possible.
Microsoft SQL Server offers native capabilities to process XML data. For instance, it is possible to supply an appropriately formatted XML document directly to a stored procedure for processing, thanks the OPENXML and other various T-SQL constructs.
That’s why I opted to bypass any pre-processing on the supplied XML document and hand it over directly to the stored procedure. This stored procedure would, in turn, iterate over the different records and process them accordingly.
In order to do that, the contents of the XML document must be specified in a single argument to the stored procedure, like so.
Serializing the entire contents of an XML document with the BizTalk Mapper
In my solution, the stored procedure is invoked through the WCF-SQL adapter. Therefore, a special document that conforms to an adapter-specific generated schema must be sent to the corresponding solicit-response send port.
In order to map the entire contents of an XML subtree structure from the source schema to a single element in the target schema, one can use the Call Template functoid in a BizTalk map.
How does it Work?
In the Call Template functoid, notice the use of two different templates.
The first one, is the entry-point for the Call Template functoid when running the map. It declares a single parameter that corresponds to the node on the source document under which the entire structure must be serialized to the target node.
<!--Uncomment the following xslt for a sample Xslt Call Template that creates a Field element whose value is the concatenatation of the two inputs. Change the number of parameters of this template to be equal to the number of inputs connected to this functoid.--> <xsl:template name="called-template"> <xsl:param name="param1" /> <xsl:element name="xmlDocument" namespace="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo"> <xsl:text disable-output-escaping="yes"><![CDATA[</xsl:text> <xsl:call-template name="identity" /> <xsl:text disable-output-escaping="yes">]]></xsl:text> </xsl:element> </xsl:template>
First, the template produces an XML element, based upon its name and namespace as required in the target schema. In this case, the element corresponds to the name of the argument to the stored procedure as generated by the WCF-SQL adapter.
Notice that the serialized data is wrapped around a <!CDATA[]]> tag, so as to eliminate the need to escape XML-reserved characters.
The serialized data itself is produced by calling the second template. This one is none other than the identity template.
<xsl:template name="identity" match="@*|node()"> <xsl:copy> <xsl:apply-templates select="@*|node()" /> </xsl:copy> </xsl:template>
This template iterates over all elements, texts, comments and processing instructions and recursively copies the contents to produce its output.
