none
error while trying to insert XML data into SQL server via WCF SQL adapter RRS feed

  • Question

  • Hi ,

    I am getting an error while trying to insert XML data into SQL server via WCF SQL adapter.

    I have a Stored Procedure which takes an XML document as an input parameter. 

    I have to pass an XML to the Stored Procedure. When i generate the Schema using the WCF adapter, the parameter comes as a String type 
    in the Schema. 

    When i try to to set the value of that element in the Schema as the Outer Xml of the XML message, i get an error, since a string data type cannot take that amount of data.

    Could you suggest a way to pass this xml to the SQL. 

    I am using BizTalk Server 2009 and SQL server 2005

    Regards,
    Ujjwal
    Friday, April 16, 2010 11:11 AM

Answers

  • What is the datatype of the parameter on the proc? I would use varchar(max). If you are not using varchar(max), this is one of the problems.

    Also, are trying to set this in an expression shape in your BizTalk orchestration?

    I would try using a .NET reference method that takes the XmlDocument that has the new data and returns an XmlDocument with the populated message. Within the method you could use an XmlTextWriter to stream the contents into the XmlDocument for the stored proc message call. Then in a message assignment shape you can implicitly convert the returned XmlDocument into a BizTalk message.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Friday, April 16, 2010 6:19 PM
    Moderator

All replies

  • Hi Ujjwal,

    I suggest to review this thread explaining to have you xml loaded in XmlDocument.

    HTH

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Friday, April 16, 2010 11:39 AM
    Moderator
  • Hi Greg,

    Thanks for you quick response. I've read the thread that you've suggested. Even they are passing the XML Document as a string.

    "storedProcMessage.bts_Load_XmlData.loaddata = xmlDocumentVariable.OuterXml;"

     Since my xml document is huge, i cannot pass the outer xml to the parameter because of the size constraints of the String Data Type.

     

    Could you suggest any other way of pushing this xml to SQL. 

    Friday, April 16, 2010 11:54 AM
  • What is the datatype of the parameter on the proc? I would use varchar(max). If you are not using varchar(max), this is one of the problems.

    Also, are trying to set this in an expression shape in your BizTalk orchestration?

    I would try using a .NET reference method that takes the XmlDocument that has the new data and returns an XmlDocument with the populated message. Within the method you could use an XmlTextWriter to stream the contents into the XmlDocument for the stored proc message call. Then in a message assignment shape you can implicitly convert the returned XmlDocument into a BizTalk message.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Friday, April 16, 2010 6:19 PM
    Moderator
  • Thanks a lot Ben. This solved my issue. Like you correctly pointed out, I was trying to create the request message in an expression editor. I now used the approach that you've suggested via . Net method call. This works well :) Thanks for your timely help and advise

    Thanks Steef your inputs :)

    Saturday, April 17, 2010 2:20 PM
  • Hi UjjwalDevarapalli or Ben,

    I am facing the same task, could you please post some sample code on how to do it via the Net method call?

    Thanks a lot!

     

    Wednesday, June 2, 2010 7:16 PM
  • GoGo, yes, I can try to get something put together for you later today. Thanks.
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Friday, June 4, 2010 2:04 PM
    Moderator
  • Hi GoGo,

    Have a look at following sample code, hope it helps you :

     

     

     

     

    public static XmlDocument BuildArchiveMessage(string invoiceUniqueID, XLANGMessage message)
            {
                string ns = "http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo";
                MemoryStream stream = new MemoryStream();
                XmlDocument xmlArchiveDoc = new XmlDocument();
                XmlDocument xmlInvoiceDoc = (XmlDocument)message[0].RetrieveAs(typeof(XmlDocument));

                using (XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8))
                {

                    writer.WriteStartDocument();
                    writer.WriteStartElement("arc", "usp_InsertXmlRawData", ns);
                    writer.WriteStartElement("arc", "InvoiceUniqueID", ns);
                    writer.WriteString(invoiceUniqueID);
                    writer.WriteEndElement();
                    writer.WriteStartElement("arc", "RawData", ns);
                    writer.WriteString(xmlInvoiceDoc.OuterXml);
                    writer.WriteEndElement();
                    writer.WriteEndElement();
                    writer.WriteEndDocument();
                    writer.Flush();

                    stream.Seek(0, SeekOrigin.Begin);

                    xmlArchiveDoc.Load(stream);

                }

                return xmlArchiveDoc;
            }
    Friday, June 25, 2010 6:07 AM