none
How to insert the XML Doc into the SQL 2008 RRS feed

  • Question

  • Hi, 

     

    I am having EDI transaction set coming into the biztalk from the receive location which contains more than 5 to 6 messages in the transaction set I am receiving. I splitted this transaction set in to the xml messages by using EDI Pipeline and I inserted the data from xml into the Database.

    Also I have to insert the XML doc into the database in one of the record in the record set. 

    So, I want to know, how to insert the XML Doc (which I am receiving to the BizTalk after passing through the EDI receive pipeline) into the Database.

    Please anybody help me on this...

    Thanks in Advance

    Wednesday, September 15, 2010 4:13 AM

Answers

  • Follow these steps and you will get the data in the database.
    1. Assing the uncoming message in a message assignment to a message that will have a message type of System.Xml.XmlDocument ( xml = inboundmessage;) Have that in your message assignment where xml is System.Xml.XmlDocument

     2. Create a DLL that will be used to pass the message. Example below:
      public static void insert_xml(string source_xml)
            {
                SqlConnection conn = new SqlConnection("Data Source=[Server]; Initial Catalog=[dbo.tablenme]; Integrated Security=SSPI;");
                SqlConnection conn = GetConnection();

                {

                    //********************************************
                    string sql = "";
                    SqlParameter param = new SqlParameter();
                    DataTable Param_dt = new DataTable();
                    DataRow myNewRow;
                    //********************************************

                    //******************************************************
                    // 1. Open Database Connection
                    //******************************************************
                    SqlConnection Myconn = GetConnection();
                    //

                    {
              
                        // 1. declare command object with parameter

                        SqlCommand command = new SqlCommand("exec insert_xml @source_xml", Myconn);

                        command.Parameters.Add(new SqlParameter("@source_xml", source_xml));
          
                        // get data stream
                        command.ExecuteScalar();

                        Myconn.Close();
                        Myconn.Dispose();


                    }

                }
            }
    (FYI if you are more familiar with C#, use references to the data source instead of hardcoding. It is better practice.
    3. Create a stored procedure that will be called by the DLL (insert_xml )
    4. The table must have the column set to xml.
    5. On the orchestration, call the DLL after you completed all the steps. Dont forget to add the reference to the project. Use the expression as you see below:
    BiztalkFunctions.businessrules.insert_xml(esourceXml);


    Thats it. I personally like to do it this way because it is much more robust than other methods. Good luck.


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    • Proposed as answer by Carlos T. _ Wednesday, September 15, 2010 12:02 PM
    • Marked as answer by Andrew_ZhuModerator Wednesday, September 22, 2010 7:55 AM
    Wednesday, September 15, 2010 12:01 PM
  • Completely understand. I was there too, and still learning the product today. I know the steps may seem confusing but think about it like this. You have to assign th incoming message to an System.Xml.XmlDocument before you can pass it to the database. The message that is comming in, for your case EDI837PMsg is matched with the X12_00401_837_P schema. Therefore you have to assing EDI837PMsg to message XMLMsg just like this "XMLMsg = EDI837PMsg; inside ta construct message where you are constructing the XMLMsg message. Now you have the xml document that you will pass to the DLL.

    On an expression shape, you will call the DLL that you created that will insert the messsage XMLMsg into the database. Thefore, your DLL will be called inside the expression shape just like this "DLLNAME.businessrules.insert_xml(XMLMsg);" Of course this is taking into account that you have already created the DLL which you will need to add as a reference to the solution. The DLL will basically pass that message into a stored procedure that will pass it to SQL. Use the code above for the DLL.

    Let me know if you have any questions.


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Thursday, September 16, 2010 12:01 PM

All replies

  • Follow these steps and you will get the data in the database.
    1. Assing the uncoming message in a message assignment to a message that will have a message type of System.Xml.XmlDocument ( xml = inboundmessage;) Have that in your message assignment where xml is System.Xml.XmlDocument

     2. Create a DLL that will be used to pass the message. Example below:
      public static void insert_xml(string source_xml)
            {
                SqlConnection conn = new SqlConnection("Data Source=[Server]; Initial Catalog=[dbo.tablenme]; Integrated Security=SSPI;");
                SqlConnection conn = GetConnection();

                {

                    //********************************************
                    string sql = "";
                    SqlParameter param = new SqlParameter();
                    DataTable Param_dt = new DataTable();
                    DataRow myNewRow;
                    //********************************************

                    //******************************************************
                    // 1. Open Database Connection
                    //******************************************************
                    SqlConnection Myconn = GetConnection();
                    //

                    {
              
                        // 1. declare command object with parameter

                        SqlCommand command = new SqlCommand("exec insert_xml @source_xml", Myconn);

                        command.Parameters.Add(new SqlParameter("@source_xml", source_xml));
          
                        // get data stream
                        command.ExecuteScalar();

                        Myconn.Close();
                        Myconn.Dispose();


                    }

                }
            }
    (FYI if you are more familiar with C#, use references to the data source instead of hardcoding. It is better practice.
    3. Create a stored procedure that will be called by the DLL (insert_xml )
    4. The table must have the column set to xml.
    5. On the orchestration, call the DLL after you completed all the steps. Dont forget to add the reference to the project. Use the expression as you see below:
    BiztalkFunctions.businessrules.insert_xml(esourceXml);


    Thats it. I personally like to do it this way because it is much more robust than other methods. Good luck.


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    • Proposed as answer by Carlos T. _ Wednesday, September 15, 2010 12:02 PM
    • Marked as answer by Andrew_ZhuModerator Wednesday, September 22, 2010 7:55 AM
    Wednesday, September 15, 2010 12:01 PM
  • Hi Carlos,

    Thanks for your Reply,

    I am starter for the biztalk and I am unable to understand what you said. Can you please explain the same in eloberated. I am sorry for this.

    I have following Messages in my project

    a.EDI837PMsg which is "X12_00401_837_P" Schema
    b.SQLReqMsg which is Procedure Request Message (to insert the data from the xml to the DB) which was generated by the stored procedure which is xontinig one field as the XML Document.
    c.XMLMsg
    I assigned System.Xml.XmlDocument to the Message "XMLMsg " and also added the Variable "VarXMLDoc" to the System.Xml.XmlDocument.
    So now  to which incoming message I have to assign this XMLMsg.? Is it EDI837PMsg or SQLReqMsg?

    I created a message Assignment shape and in this which messaege I have to construct?

    and what is the exact expression that I have to give to the Message Assignment shape

    Also I had a table which have the column set to the xml.  Can you please let me know the exact stored procedure which I have to write.

    Please help me out in this..

    Thanks alot for your help...

    Wednesday, September 15, 2010 4:05 PM
  • Completely understand. I was there too, and still learning the product today. I know the steps may seem confusing but think about it like this. You have to assign th incoming message to an System.Xml.XmlDocument before you can pass it to the database. The message that is comming in, for your case EDI837PMsg is matched with the X12_00401_837_P schema. Therefore you have to assing EDI837PMsg to message XMLMsg just like this "XMLMsg = EDI837PMsg; inside ta construct message where you are constructing the XMLMsg message. Now you have the xml document that you will pass to the DLL.

    On an expression shape, you will call the DLL that you created that will insert the messsage XMLMsg into the database. Thefore, your DLL will be called inside the expression shape just like this "DLLNAME.businessrules.insert_xml(XMLMsg);" Of course this is taking into account that you have already created the DLL which you will need to add as a reference to the solution. The DLL will basically pass that message into a stored procedure that will pass it to SQL. Use the code above for the DLL.

    Let me know if you have any questions.


    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Thursday, September 16, 2010 12:01 PM