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.
http://grounding.co.za/blogs/romiko/archive/2008/03/26/biztalk-2006-default-sql-adapter-updategrams-part-1.aspx
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.