- This topic has 8 replies, 1 voice, and was last updated 8 years, 4 months ago by
.
-
Posts
-
-
So I am fairly new to BizTalk and I have a need for one of my projects that requires taking an XML document submitted to us via AS2, taking that document and passing it to a stored procedure that has an XML parameter. My biggest problem is understanding how to send the XML file to SQL.
I have read some of the other posts out there that kind of follow this topic, but I really don’t understand exactly what needs to be done. Are there any samples out there? Any step by step guides? Any help you could give would be… helpful.
BTW… I know that there are other ways to do this without BizTalk but this is the requirement I was given.
-
I have worked with this area a lot, and have the scars to prove it… (all mental)!
Anyway, the sql adapter metadata generator automatically generates the insert schema that it uses to call stored procs.
You will notice that all parameters are generated as attributes with simple data types, and if you are using the xml data type the attribute gets created as a string. The attribute can not contain xml. This means to insert xml using the sql adapter you must to convert your xml data to string which means you will need to escape out the reserved characters such as < > ” ‘.
This is a non trivial task.
There are various horrible ways of doing this,
you can write some terribly complicated xslt as can be seen here.
create your insert message using a .net object based on the generated schema using xsd.exe, then assign your xml string to the property in th e.net object and let the serializer do the escaping for you (this is a very memory hungry method and not suitable for large xml files), see my article here
You should note that if the document you are trying to insert is huge (35 mb +) you may run into memory issues with teh sql adapter. I have encountered this and it was a trauma to work around.
The following blog helped me a great deal, read through to part 3 where there is a link to a custom adapter that removes this problem to a large degree.
-
This is not really that formidable. You have the use of an Xml Parser that will do all the grunt work for you. When you assign any string to an xml node the parser will automatically do all the required escaping for you. When you retrieve the text it will be unescaped for you.
In an orchestration you can assign the message to an XmlDocument variable and the assign the XmlDocument.InnerText to a distinguished field on the SQL message e.g.
xmlDoc = inputMessage;
SQLMessage.XmlDataField = xmlDoc.InnerText;If you want to do this in a map, you can create an external assembly like this:
public class ConvertNode
{
public static string ToXmlString(XPathNodeIterator node)
{
return node.Current.OuterXml;
}
}and call it from some custom Xslt (you need to declare your Custom Extension Xml as appropriate)
<xsl:element name=”XmlDataField”>
<xsl:value-of select=”ConvertNode:ToXmlString(.)”/>
</xsl:element>-
I am still having issues with this, anyone have a sample or can point me to a step by step guide. I can’t seem to get it to work
-
I have tried the orchestration method, here is what I have done in my orchestration:
I have my receive message that I pass to a constructor, I do a Transform with nothing in it, then I do a Assign where I set a variable (XMLDocument type) to my receive message. I also set the distinguished field of my SQL Request schema, (inputXML) to the innerXML of the xmlDoc variable i set in the previous line.
Ok everything seems to make sense… but when I run it… I get an error “Could not find stored procedure ‘header'” Which is an element of my xml document
So I am guessing that its messing up the message going into SQL some how, but I don’t understand why.
-
I also has same requirement.
So what i did was created a Stored procedure in SQL (containin XML parameter)..
When i added it in biztalk, the schema came as attribute of type string.
so, here i created a schema which contains an input element of type string.
i converted the entire xml message in to string and then applied Base64 encoding to it
i.e.str = System.Convert.ToBase64String(
System.Text.Encoding.ASCII.GetBytes(objXML.OuterXml));so now str contains Base64String.
now create a map that maps our created schema and SQL generated schema.
map str to the string given in it by scripting functiod.
i.e. wriite
public string Base64ToXml(string msg)
{
return System.Text.Encoding.ASCII.GetString(
System.Convert.FromBase64String(msg));
}in functoid inline C#.
Now, It il work 🙂 🙂
-
if still u have any pron u can contact me on <[email protected]>
-
-
-
http://www.21cssindia.com/…/biztalk-server-online-training-213.html
Biztalk Server Online Training
Click Here For Enquiry
COURSE OUT LINE
Introductions to Enterprise Application Patterns and BizTalk Server, Understanding BizTalk Framework, Setting up a BizTalk Server Environment, Messaging Architecture, Setting up a BizTalk Server Environment Engine, Business Activity Monitoring, WCF Services Schemas in BizTalk, Transformations in BizTalk, The BizTalk Messaging Engine and Pipelines, Adapters in BizTalk, Orchestrations, Advanced Orchestrations, Integration Patterns in BizTalkBizTalk Rules with BizTalk, Testing BizTalk Artifacts, BizTalk Server Instrumentation, Error Handling, and Deployment,Tracking and Deploying BizTalk Solutions, Monitoring and Maintenance, Administration, BizTalk Server Performance andTuning, BizTalk Tools, BizTalk 2013 Features, ESB Toolkit 2.
-
-
-
-
Biztalk Server Online Training
COURSE OUT LINE
Introductions to Enterprise Application Patterns and BizTalk Server, Understanding BizTalk Framework, Setting up a BizTalk Server Environment, Messaging Architecture, Setting up a BizTalk Server Environment Engine, Business Activity Monitoring, WCF Services Schemas in BizTalk, Transformations in BizTalk, The BizTalk Messaging Engine and Pipelines, Adapters in BizTalk, Orchestrations, Advanced Orchestrations, Integration Patterns in BizTalkBizTalk Rules with BizTalk, Testing BizTalk Artifacts, BizTalk Server Instrumentation, Error Handling, and Deployment,Tracking and Deploying BizTalk Solutions, Monitoring and Maintenance, Administration, BizTalk Server Performance andTuning, BizTalk Tools, BizTalk 2013 Features, ESB Toolkit 2.
http://www.21cssindia.com/…/biztalk-server-online-training-213.html
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.