BizTalk: SQL Send Port: How to Pass Unbounded Nodes to the SQL Stored Procedure

Article
1/17/2024

If we need to pass and process ONE row to the SQL store proc, it is pretty easy.

Map the XML message (inside BizTalk) elements to the service SQL request schema elements. This schema is created by SQL Port wizard when we point it to the SQL store proc.

More interesting problem is to pass several rows to the SQL Send port. Here I use the SQL terminology. In Xml terms it means the Xml node with MaxOccurs=unbounded in the Xml document. (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 how to use a SQL storproc for this.)

We cannot pass the rowset to the storeproc as a parameter, but we can use workaround.

We serialize the Xml document with unbounded node(s) to the string .
We pass this string to the store proc 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 to rowset.
Process this rowset into store proc.

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 sample source code:

[text of the expression in the Message Assignment shape 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..."

// Distinguish an attribute the "Xml_Text" in the schema " http://....Schemas.Insert_to_Table#Insert_to_Table_Request!

var_XmlDocument = msg_EDICanonical;

msg_BTS_ProcessEDIDocumentRequest.parameters.BTS_ProcessEDIDocument.Xml_Text = var_XmlDocument.OuterXml;

[text of the stored proc]

-- ===========================================================
-- 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:

-- Usually 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>sdfsd fdfsdf</voyage><vessel_name>sdf gdfg</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

*/

Share via

BizTalk: SQL Send Port: How to Pass Unbounded Nodes to the SQL Stored Procedure

See Also

Additional resources