Hello Folks,
Ok, another way we can actually send data to SQL server using BizTalk’s default SQL Adapter is actually by calling a stored procedure by using an XML Template, weird as it sounds, it is the only method supported by the default SQL Adapter, unless you want to resort to Updategrams, which we covered in Part 1. I assume you all been working and playing with BizTalk for while to know how to setup routing and subscriptions etc.
Ok, first thing we need is a Customer table and a primitive stored procedure that will insert data into the Customer Table.
|
USE [Test] GO /****** Object: Table [dbo].[Customer] Script Date: 04/05/2008 16:02:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customer]( [CustomerID] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [nvarchar](30) NOT NULL, [Address] [nvarchar](60) NOT NULL, [City] [nvarchar](30) NOT NULL, [Region] [nvarchar](30) NOT NULL, [PostalCode] [nvarchar](10) NOT NULL, [RecordStatus] [tinyint] NOT NULL ) ON [PRIMARY] |
Primitive Stored Procedure that we will call from BizTalk
|
USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[BizTalkToCustomerInsert] @CustomerName nvarchar(60) , @Address nvarchar(120) , @City nvarchar(60) , @Region nvarchar(60) , @PostalCode nvarchar(60) AS
BEGIN Declare @ReturnCode int select @ReturnCode = 0
Insert Customer (CustomerName, Address, City, Region, PostalCode) Values (@CustomerName, @Address, @City, @Region, @PostalCode)
select @ReturnCode = @@error
return @ReturnCode END |
Now when you create a BizTalk project you will need to generate an XML template that will be used to call the stored procedure, I will show you exactly how this is accomplished, for now, here is a sneak peak at the XML template used to call the stored procedure. I decided to put it here, so you get an idea of what we are actually creating and why.
XML Stored Procedure
|
<?xml version="1.0" encoding="utf-16" ?> – <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://SQLAdapterStoredProcedure.schCustomer" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema"> – <xs:annotation> – <xs:appinfo> <msbtssql:sqlScript value="exec [BizTalkToCustomerInsert] @Address=" ", @City=" ", @CustomerName=" ", @PostalCode=" ", @Region=" "" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" /> </xs:appinfo> </xs:annotation> – <xs:element name="Customer_Request"> – <xs:complexType> – <xs:sequence> – <xs:element name="BizTalkToCustomerInsert"> – <xs:complexType> <xs:attribute name="Address" type="xs:string" /> <xs:attribute name="City" type="xs:string" /> <xs:attribute name="CustomerName" type="xs:string" /> <xs:attribute name="PostalCode" type="xs:string" /> <xs:attribute name="Region" type="xs:string" /> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> – <xs:element name="Customer_Response"> – <xs:complexType> – <xs:sequence> <xs:element name="Success" type="xs:anyType" /> </xs:sequence> </xs:complexType> </xs:element> </xs:schema> |
So, as you can see, to configure a SQL Adapter to call this procedure, we have created a template. You wondering how you would parse parameters into the store procedure xml document, the way this is accomplished is by using a simple map.
The SQL Adapter configuration will look like this.
Notice the Document Target Namespace is the same as the XML Template: targetNamespace="http://SQLAdapterStoredProcedure.schCustomer
So how actually did the XML schema get defined. Well what happens is that you right click your BizTalk project in Visual Studio and Click Add Generated Items.
The you click Add Adapter Metadata
Click SQL and then click Next
Configure the connection string to the database where the customer table resides.
This part is rather important, be careful to name your target namespace and root element, so you can correctly identify the documents for effective message routing, I keep it simple here, but at work we use something like:
CompanyName.Application.DocumentFunction
e.g. ACME.CRM.InsertCustomerRecords
So as you can see this wizard is basically creating the XSD document I showed earlier on for you.
Click Next and click Stored Procedure.
Now choose the procedure, notice you can provide default values!
Click generate
Click next and complete the wizard.
That’s basically how you create a XML call for a stored procedure. To wrap it up, you can have an orchestration subscribe to some sort of Customer XML Record and then send it to the SQL Adapter, but first using a map to transform it to the Schema we just made.
So maybe you had a customer schema, we you have a receive port where new customer records are dropped.
|
<?xml version="1.0" encoding="utf-16" ?> – <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://SQLAdapterStoredProcedure.schCustomer" targetNamespace="http://SQLAdapterStoredProcedure.schCustomer" xmlns:xs="http://www.w3.org/2001/XMLSchema"> – <xs:element name="Customer"> – <xs:complexType> – <xs:sequence> <xs:element name="CustomerName" type="xs:string" /> <xs:element name="Address" type="xs:string" /> <xs:element name="City" type="xs:string" /> <xs:element name="Region" type="xs:string" /> <xs:element name="PostalCode" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:schema> |
And the map I showed a few moments ago can transform this information to call the stored procedure.
Here is what the Orchestration looks like.
Summary
Personally, I would never use this method in a production environment. For starters I dislike the whole idea of create xml templates and maps just to call a stored procedure, and even more, I dislike using orchestrations very much, granted we could get rid of the orchestration and use a send port to subscribe to customer documents and do the mapping and send it to the SQL adapter, but what a fuss.
Also, imagine, you developed a Customer table like this:
|
USE [Test] GO /****** Object: Table [dbo].[cs] Script Date: 04/05/2008 16:29:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[cs]( [cs_id] [bigint] IDENTITY(1,1) NOT NULL, [xml_data] [xml] NULL, [source_name] [nvarchar](50) NULL, [date_inserted] [smalldatetime] NULL DEFAULT (getdate()), [stage] [nvarchar](20) NULL, [status] [nvarchar](20) NULL ) ON [PRIMARY] |
Do you notice something here, my data is stored as XML in the SQL Table, So think for a moment, the map I showed you previously would not have a 1 to 1 mapping per record element in the XML. What you would want is take the Customer XML on the left hand side and shove the whole document into an attribute on the right, since the store procedure used to call requires XML Data like this:
|
USE [Test] GO /****** Object: StoredProcedure [dbo].[set_records] Script Date: 04/05/2008 16:31:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[set_records] @Data XML–, @status NVARCHAR(20) AS Declare @status nvarchar(20) set @status = ‘Processed’ IF @Data IS NULL OR @status IS NULL RETURN
–move to next record if status = success IF @status = ‘Processed’ BEGIN UPDATE dbo.cs SET xml_data = @Data, stage = dbo.myroute(cs.source_name, cs.stage, cs.status), status = case dbo.myroute(cs.source_name, cs.stage, cs.status) WHEN cs.stage THEN @status ELSE N‘Unprocessed’ END FROM dbo.cs cs JOIN @Data.nodes(‘declare default element namespace "http://SQLAdapterGetRecords.Record";Record/Row/Metadata’) as tab(col) ON tab.col.value(‘text()[1]’, ‘BIGINT’) = cs.cs_id END ELSE BEGIN UPDATE dbo.cs SET status = @status FROM dbo.cs cs JOIN @Data.nodes(‘declare default element namespace "http://SQLAdapterGetRecords.Record";Record/Row/Metadata’) as tab(col) ON tab.col.value(‘text()[1]’, ‘BIGINT’) = cs.cs_id END |
I more realistic stored procedure, you see, we want the stored procedure to actually take an entire XML Document, and the only way to do this with a BizTalk Map is using XSLT. Like this:
Notice in the properties window, that I have a path to an XSL File, Procedure_xslt.xsl.
So in the example above I put all the XML data on the left into an attribute on the right called xml. This is what the output of the transform will look like:
Input Document (Left): (You could have used the customer XML schema, I decided to choose another one, to show you how useful the new procedure is, it could be used to store xml data for customer or any other xml data.
|
– < ns0:Record xmlns:ns0="http://SQLAdapterGetRecords.Record"> – < ns0:Row> < ns0:test>test_0</ns0:test> < ns0:another>another_0</ns0:another> < ns0:Metadata>Metadata_0</ns0:Metadata> </ns0:Row> </ns0:Record> |
Output Document (Right):
|
<InsertRecord xmlns="http://SQLAdapterSetRecords.Record"> <set_records status="Processed" xml="<ns0:Record xmlns:ns0="http://SQLAdapterGetRecords.Record"> <ns0:Row><ns0:test>test_0</ns0:test><ns0:another>another_0</ns0:another> <ns0:Metadata>Metadata_0</ns0:Metadata></ns0:Row></ns0:Record>" /> </InsertRecord> |
Notice the document on the left is now the attribute value in the XML, see above.
Nice is it not, all the XML data on left is now an attribute on the right, by using an XSLT transform. Here is the template:
|
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output omit-xml-declaration="yes"/>
<xsl:param name="use-empty-syntax" select="true()"/> <xsl:param name="exclude-unused-prefixes" select="true()"/>
<xsl:param name="start-tag-start" select="‘<‘"/> <xsl:param name="start-tag-end" select="‘>’"/> <xsl:param name="empty-tag-end" select="‘/>’"/> <xsl:param name="end-tag-start" select="‘</’"/> <xsl:param name="end-tag-end" select="‘>’"/> <xsl:param name="space" select="‘ ‘"/> <xsl:param name="ns-decl" select="‘xmlns’"/> <xsl:param name="colon" select="‘:’"/> <xsl:param name="equals" select="‘=’"/> <xsl:param name="attribute-delimiter" select="‘"‘"/> <xsl:param name="comment-start" select="‘<!–‘"/> <xsl:param name="comment-end" select="‘–>’"/> <xsl:param name="pi-start" select="‘<?’"/> <xsl:param name="pi-end" select="‘?>’"/>
<xsl:template name="xml-to-string"> <xsl:param name="node-set" select="."/> <xsl:apply-templates select="$node-set" mode="xml-to-string"> <xsl:with-param name="depth" select="1"/> </xsl:apply-templates> </xsl:template>
<xsl:template match="/" name="xml-to-string-root-rule"> <InsertRecord xmlns="http://SQLAdapterSetRecords.Record"> <set_records status="Processed"> <xsl:attribute name="xml"> <xsl:call-template name="xml-to-string"/> </xsl:attribute> </set_records> </InsertRecord>
</xsl:template>
<xsl:template match="/" mode="xml-to-string"> <xsl:param name="depth"/> <xsl:apply-templates mode="xml-to-string"> <xsl:with-param name="depth" select="$depth"/> </xsl:apply-templates> </xsl:template>
<xsl:template match="*" mode="xml-to-string"> <xsl:param name="depth"/> <xsl:variable name="element" select="."/> <xsl:value-of select="$start-tag-start"/> <xsl:call-template name="element-name"> <xsl:with-param name="text" select="name()"/> </xsl:call-template> <xsl:apply-templates select="@*" mode="xml-to-string"/> <xsl:for-each select="namespace::*"> <xsl:call-template name="process-namespace-node"> <xsl:with-param name="element" select="$element"/> <xsl:with-param name="depth" select="$depth"/> </xsl:call-template> </xsl:for-each> <xsl:choose> <xsl:when test="node() or not($use-empty-syntax)"> <xsl:value-of select="$start-tag-end"/> <xsl:apply-templates mode="xml-to-string"> <xsl:with-param name="depth" select="$depth + 1"/> </xsl:apply-templates> <xsl:value-of select="$end-tag-start"/> <xsl:call-template name="element-name"> <xsl:with-param name="text" select="name()"/> </xsl:call-template> <xsl:value-of select="$end-tag-end"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="$empty-tag-end"/> </xsl:otherwise> </xsl:choose> </xsl:template>
<xsl:template name="process-namespace-node"> <xsl:param name="element"/> <xsl:param name="depth"/> <xsl:variable name="declaredAbove"> <xsl:call-template name="isDeclaredAbove"> <xsl:with-param name="depth" select="$depth – 1"/> <xsl:with-param name="element" select="$element/.."/> </xsl:call-template> </xsl:variable> <xsl:if test="(not($exclude-unused-prefixes) or ($element | $element//@* | $element//*)[namespace-uri()=current()]) and not(string($declaredAbove)) and name()!=’xml’"> <xsl:value-of select="$space"/> <xsl:value-of select="$ns-decl"/> <xsl:if test="name()"> <xsl:value-of select="$colon"/> <xsl:call-template name="ns-prefix"> <xsl:with-param name="text" select="name()"/> </xsl:call-template> </xsl:if> <xsl:value-of select="$equals"/> <xsl:value-of select="$attribute-delimiter"/> <xsl:call-template name="ns-uri"> <xsl:with-param name="text" select="string(.)"/> </xsl:call-template> <xsl:value-of select="$attribute-delimiter"/> </xsl:if> </xsl:template>
<xsl:template name="isDeclaredAbove"> <xsl:param name="element"/> <xsl:param name="depth"/> <xsl:if test="$depth > 0"> <xsl:choose> <xsl:when test="$element/namespace::*[name(.)=name(current()) and .=current()]">1</xsl:when> <xsl:when test="$element/namespace::*[name(.)=name(current())]"/> <xsl:otherwise> <xsl:call-template name="isDeclaredAbove"> <xsl:with-param name="depth" select="$depth – 1"/> <xsl:with-param name="element" select="$element/.."/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:if> </xsl:template>
<xsl:template match="@*" mode="xml-to-string"> <xsl:value-of select="$space"/> <xsl:call-template name="attribute-name"> <xsl:with-param name="text" select="name()"/> </xsl:call-template> <xsl:value-of select="$equals"/> <xsl:value-of select="$attribute-delimiter"/> <xsl:call-template name="attribute-value"> <xsl:with-param name="text" select="string(.)"/> </xsl:call-template> <xsl:value-of select="$attribute-delimiter"/> </xsl:template>
<xsl:template match="comment()" mode="xml-to-string"> <xsl:value-of select="$comment-start"/> <xsl:call-template name="comment-text"> <xsl:with-param name="text" select="string(.)"/> </xsl:call-template> <xsl:value-of select="$comment-end"/> </xsl:template>
<xsl:template match="processing-instruction()" mode="xml-to-string"> <xsl:value-of select="$pi-start"/> <xsl:call-template name="pi-target"> <xsl:with-param name="text" select="name()"/> </xsl:call-template> <xsl:value-of select="$space"/> <xsl:call-template name="pi-text"> <xsl:with-param name="text" select="string(.)"/> </xsl:call-template> <xsl:value-of select="$pi-end"/> </xsl:template>
<xsl:template match="text()" mode="xml-to-string"> <xsl:call-template name="text-content"> <xsl:with-param name="text" select="string(.)"/> </xsl:call-template> </xsl:template>
<xsl:template name="element-name"> <xsl:param name="text"/> <xsl:value-of select="$text"/> </xsl:template>
<xsl:template name="attribute-name"> <xsl:param name="text"/> <xsl:value-of select="$text"/> </xsl:template>
<xsl:template name="attribute-value"> <xsl:param name="text"/> <xsl:variable name="escaped-markup"> <xsl:call-template name="escape-markup-characters"> <xsl:with-param name="text" select="$text"/> </xsl:call-template> </xsl:variable> <xsl:choose> <xsl:when test="$attribute-delimiter = "‘""> <xsl:call-template name="replace-string"> <xsl:with-param name="text" select="$escaped-markup"/> <xsl:with-param name="replace" select=""‘""/> <xsl:with-param name="with" select="‘&apos;’"/> </xsl:call-template> </xsl:when> <xsl:when test="$attribute-delimiter = ‘"‘"> <xsl:call-template name="replace-string"> <xsl:with-param name="text" select="$escaped-markup"/> <xsl:with-param name="replace" select="‘"‘"/> <xsl:with-param name="with" select="‘&quot;’"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="replace-string"> <xsl:with-param name="text" select="$escaped-markup"/> <xsl:with-param name="replace" select="$attribute-delimiter"/> <xsl:with-param name="with" select="”"/> </xsl:call-template> </xsl:otherwise> </xsl:choose> </xsl:template>
<xsl:template name="ns-prefix"> <xsl:param name="text"/> <xsl:value-of select="$text"/> </xsl:template>
<xsl:template name="ns-uri"> <xsl:param name="text"/> <xsl:call-template name="attribute-value"> <xsl:with-param name="text" select="$text"/> </xsl:call-template> </xsl:template>
<xsl:template name="text-content"> <xsl:param name="text"/> <xsl:call-template name="escape-markup-characters"> <xsl:with-param name="text" select="$text"/> </xsl:call-template> </xsl:template>
<xsl:template name="pi-target"> <xsl:param name="text"/> <xsl:value-of select="$text"/> </xsl:template>
<xsl:template name="pi-text"> <xsl:param name="text"/> <xsl:value-of select="$text"/> </xsl:template>
<xsl:template name="comment-text"> <xsl:param name="text"/> <xsl:value-of select="$text"/> </xsl:template>
<xsl:template name="escape-markup-characters"> <xsl:param name="text"/> <xsl:variable name="ampEscaped"> <xsl:call-template name="replace-string"> <xsl:with-param name="text" select="$text"/> <xsl:with-param name="replace" select="‘&‘"/> <xsl:with-param name="with" select="‘&amp;’"/> </xsl:call-template> </xsl:variable> <xsl:variable name="ltEscaped"> <xsl:call-template name="replace-string"> <xsl:with-param name="text" select="$ampEscaped"/> <xsl:with-param name="replace" select="‘<‘"/> <xsl:with-param name="with" select="‘&lt;’"/> </xsl:call-template> </xsl:variable> <xsl:call-template name="replace-string"> <xsl:with-param name="text" select="$ltEscaped"/> <xsl:with-param name="replace" select="‘]]>’"/> <xsl:with-param name="with" select="‘]]&gt;’"/> </xsl:call-template> </xsl:template>
<xsl:template name="replace-string"> <xsl:param name="text"/> <xsl:param name="replace"/> <xsl:param name="with"/> <xsl:variable name="stringText" select="string($text)"/> <xsl:choose> <xsl:when test="contains($stringText,$replace)"> <xsl:value-of select="substring-before($stringText,$replace)"/> <xsl:value-of select="$with"/> <xsl:call-template name="replace-string"> <xsl:with-param name="text" select="substring-after($stringText,$replace)"/> <xsl:with-param name="replace" select="$replace"/> <xsl:with-param name="with" select="$with"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:value-of select="$stringText"/> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet> |
Conclusion
In Summary I showed you the default way the SQL adapter works and its limitations when dealing with XML SQL Data Type, you would need to use XSLT and all sorts of stuff to call a store procedure that inserts XML data directly. In Part 3 we will look at writing a SQL Adapter, a custom one, Pimped to the max to support sending BizTalk XML messages directly to SQL, no maps, no transforms, no orchestrations and slow bumps to cause us pain.
I hope you enjoyed this article. See you in Part 3!
Note WCF is the best way to go now for these sort of things. best is WCF Custom with SQL binding for total control.
- Uncategorized
Romiko Derbynew 