none
SQL request RRS feed

  • Question

  • Hello.
    I have a question about biztalk and sql.
    I have a xml message i send to my sql. In this sql server i have a database called projekt. In this database i have a tabel called order.
    In this tabel a have Column named "Ordernumber" an this is a primarykey.

    My question is: In my xml message i have a field called "Ordernumber", i want to check the tabel in the sql tabel for this number.
    If the number is  not in the tabel, i want to read this message into this tabel. But if the number is in the tabel, i want to read this message into another tabel.

    Is this possible?

    Regards Nitro
    Sunday, November 29, 2009 10:04 PM

Answers

  • Hi,

    What kind of database are you using? You mention "my sql" do you mean "MySQL" or do you mean another database brand like SQL Server?

    If you mean SQL Server this is possible and relatively easy to do using BizTalk server. Answering your question in detail though would result in a very long answer. I would advise to have a look at the Biztalk Server SDK (in the BizTalk program folder) and view the examples for the SQL adapter. This will give you a good idea on how to implement this.

    Also not that if you use BizTalk 2009 you can use the WCF based version of the SQL adapter which is prefered over the "old" SQL adapter.


    HTH, Randal van Splunteren - http://biztalkmessages.vansplunteren.net - Please mark as answered if this answers your question.
    Monday, November 30, 2009 8:59 AM
    Moderator
  • Hi,

    Yes... It is quite possible using BizTalk. You can use BizTalk SQL Adapter or WCF SQL Adapter(Available only in BizTalk 2009) to serve your purpose.

    Here is a link for the samples using BizTalk SQL Adapter. Have a look at all the samples you will get an idea how to implement.

    http://blogs.msdn.com/scottwoo/archive/2004/03/11/88387.aspx

    Regards,
    Abhijit
    Abhijit Mahato Please "Mark as Answer" if Post has Answered the Question
    Monday, November 30, 2009 1:27 PM

All replies

  • Hi,

    What kind of database are you using? You mention "my sql" do you mean "MySQL" or do you mean another database brand like SQL Server?

    If you mean SQL Server this is possible and relatively easy to do using BizTalk server. Answering your question in detail though would result in a very long answer. I would advise to have a look at the Biztalk Server SDK (in the BizTalk program folder) and view the examples for the SQL adapter. This will give you a good idea on how to implement this.

    Also not that if you use BizTalk 2009 you can use the WCF based version of the SQL adapter which is prefered over the "old" SQL adapter.


    HTH, Randal van Splunteren - http://biztalkmessages.vansplunteren.net - Please mark as answered if this answers your question.
    Monday, November 30, 2009 8:59 AM
    Moderator
  • Hi,

    Yes... It is quite possible using BizTalk. You can use BizTalk SQL Adapter or WCF SQL Adapter(Available only in BizTalk 2009) to serve your purpose.

    Here is a link for the samples using BizTalk SQL Adapter. Have a look at all the samples you will get an idea how to implement.

    http://blogs.msdn.com/scottwoo/archive/2004/03/11/88387.aspx

    Regards,
    Abhijit
    Abhijit Mahato Please "Mark as Answer" if Post has Answered the Question
    Monday, November 30, 2009 1:27 PM
  • Hi,

    You need to use SQL send adapter and below I have mentioned a sample SP which reads and retrieves the data from XML to use in SP.







    CREATE PROCEDURE [dbo].[spMS_BTS_EDI_LoadTenderResponse_Process] @InputXML xml , @TestOnly int AS SET NOCOUNT ON DECLARE @ProcessedDateTime smalldatetime DECLARE @LoadTenderMast_ID int DECLARE @SCAC varchar(4) DECLARE @ShipmentID varchar(30) DECLARE @Action char(1) DECLARE @hDoc int DECLARE @ResponseTable TABLE ( ResponseCode int DEFAULT(0) , ErrorCode int , ErrorMessage varchar(4000) ) /** BEGIN TRY/CATCH **/ BEGIN TRY SET @ProcessedDateTime = GetDate() -- Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hDoc OUTPUT, @InputXML SELECT @SCAC = B201_SCAC , @ShipmentID = dbo.fnTrimLeadingZeros(B202_ShipmentID) , @Action = B104_ReservationActionCode FROM OPENXML(@hdoc, '/LoadTenderResponseRoot/LoadTenderResponse', 1) WITH (B201_SCAC varchar(4) 'B1_BeginningSegment/B201_SCAC' , B202_ShipmentID varchar(30) 'B1_BeginningSegment/B202_ShipmentID' , B104_ReservationActionCode char(1) 'B1_BeginningSegment/B104_ReservationActionCode' ) -- Remove the internal representation of the XML document. EXEC sp_xml_removedocument @hdoc End

    Vishnu
    • Edited by callvishnu Tuesday, December 1, 2009 4:32 PM Formatting
    Tuesday, December 1, 2009 4:31 PM