Leonid Ganeline - MVP
If we need to pass ONE row to the SQL storproc, it is pretty easy.
Just map the XML message (inside BizTalk) elements to the service SQL request schema elements. This schema created by SQL Port wizard when we point it to the SQL storproc.
More interesting problem is to pass several rows to the SQL port. (We can use the updategramm, but you can see this approach with all description in the BizTalk Help and I don't want to repeat it. Here I want to describe using SQL storproc.)
We cannot pass the rowset to the storeproc as a parameter, but we can use different technics.
Let's serialize the rowset to the XML.
We pass this xml to the storeproc as one parameter with Text type. (Like string "<ns0:RootNode xmlns:ns0="
http://MyNamespace">
<ElemName>...")
Inside store proc we use the OPENXML to deserialize this XML-text back to rowset.
Moreover we can serialize not only the rowset but any DataSet, say Order row and all OrderDetail rows in one Text parameter.
Below is the key source text:
=========================================================
text of the expression in the orchestration:
-------------------------------------
var_XmlDocument.LoadXml( "<ns0:Insert_to_Table_Request xmlns:ns0=\"
http://....Schemas.Insert_to_Table\"><ns0:Insert_to_Table Xml_Text=\"\" /></ns0:Insert_to_Table_Request>" );
msg_BTS_ProcessEDIDocumentRequest.parameters = var_XmlDocument; // var_XmlDocument is the "<NewDataSet><Table><railway>CNsd</railwa..."
var_XmlDocument = msg_EDICanonical;
msg_BTS_ProcessEDIDocumentRequest.parameters.BTS_ProcessEDIDocument.Xml_Text = var_XmlDocument.OuterXml;
=========================================================
text of the storeproc:
-------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Insert_to_Table]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Insert_to_Table]
GO
--================================================================================
-- Created: *******
--================================================================================
CREATE PROCEDURE dbo.Insert_to_Table
(
@Xml_Text TEXT
)
AS
SET NOCOUNT ON
-------------------------------------------------------------------------
If ( @Xml_Text Like ' ' Or @Xml_Text Like '' )
Return 0
-- ======================================= Variables: =======================================
Declare
---------------- Internal:
@i int,
@CurrentDate datetime,
@isTransactionStart int,
-- error handling:
@ErrorLogContext varchar(255),
@ErrorLogMessage varchar(500),
@EDI_History_Action varchar(50)
----------------- Temp table
Declare @Table table
(
railway varchar(50) NOT NULL
,voyage varchar(50) NOT NULL
,vessel_name varchar(50) NOT NULL
,vessel_arrive datetime NOT NULL
,vessel_depart datetime NOT NULL
,_x0032_0_count int NOT NULL
,_x0034_0_count int NOT NULL
,container_count int NOT NULL
,teu real NOT NULL
,feet int NOT NULL
)
---------------- Initializing:
set @ErrorLogContext = 'Insert_to_Table: '
--=========================== Converting XML to the Variable-tables:
-- Variable-tables have the same structure as XML records .
-- Properties Null/NotNull set up here when data is converting from XML to the Variable-tables.
set @isTransactionStart = 0
-- Calling a system stored procedure to prepare the document for calling OPENXML
set @ErrorLogMessage = 'Error converting XML to @i '
EXEC sp_xml_preparedocument @i OUTPUT, @Xml_Text, '<NewDataSet/>'
if @@error <> 0 goto ON_ERROR
-- preparing the common part of Xml document:
-- Xml variables:
set @ErrorLogMessage = 'Error Insert @Table '
Insert @Table
SELECT
isNull(railway, '')
,isNull(voyage, '')
,isNull(vessel_name, '')
,vessel_arrive
,vessel_depart
,_x0032_0_count
,_x0034_0_count
,container_count
,teu
,feet
FROM
OPENXML(@i, '/NewDataSet/Table', 2)
WITH
(
railway varchar(50)
,voyage varchar(50)
,vessel_name varchar(50)
,vessel_arrive datetime
,vessel_depart datetime
,_x0032_0_count int
,_x0034_0_count int
,container_count int
,teu real
,feet int
)
if @@error <> 0 goto ON_ERROR
set @ErrorLogMessage = 'Error EXEC sp_xml_removedocument '
EXEC sp_xml_removedocument @i
if @@error <> 0 goto ON_ERROR
-- ========================================================================
-- Transfer data from the Variable-table to DB:
-- This SP starts from the BizTalk SQL port which create transaction
-- in this case we have to handle Rollback case accordingly.
declare @MyTransaction int
set @MyTransaction = 0
If @@Trancount = 0
Begin
Begin Transaction MAIN
Set @MyTransaction = 1
End
set @isTransactionStart = 1
set @ErrorLogMessage = 'Error inserting into Table '
insert [Table]
select *
FROM @Table
if @@error <> 0 goto ON_ERROR
--==============================================================================================================
-- DONE:
If @MyTransaction = 1 and @@Trancount > 0
Begin
COMMIT work -- TRAN MAIN
End
RETURN 0
--==============================================================================================================
ON_ERROR:
If @MyTransaction = 1 and @@Trancount > 0
Begin
ROLLBACK work --TRAN MAIN
End
set @ErrorLogContext = 'Insert_to_Table: ' + @ErrorLogContext
return 1
/* test case:
declare @ret int
exec @ret = Insert_to_Table '<NewDataSet><Table><railway>sd</railway><voyage>sdfsdfdfsdf</voyage><vessel_name>sdfgdfg</vessel_name><vessel_arrive>2007-06-30 06:34:00</vessel_arrive><_x0032_0_count>17</_x0032_0_count><_x0034_0_count>8</_x0034_0_count><container_count>25</container_count><teu>33.000000</teu><feet>450</feet></Table></NewDataSet>'
select @ret -- return 0
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
=====================================================================
--
Regards,
Leonid Ganeline
BizTalk Solution Developer [MVP]
----------------------------------------------------------------------------------
Microsoft Certified Technology Specialist: BizTalk Server 2006
http://geekswithblogs.net/leonidganeline/
BlackCatBone wrote: