Re: XML Message to SQL in Attribute

You are on the right track, with sending Xml to a SQL stored procedure. I
use this technique quite often.
When you add a string to an Xml node it has to be encoded otherwise you
could create invalid Xml
e.g <name>Smith & Jones</name> is invalid Xml. It needs to be <name>Smith
&amp; Jones</name>
The XmlParser that writes the string encodes the string. Likewise when the
string is read it is decoded back to the original.
So Biztalk will send an encoded string to SQL and SQL will read and decode
the string and pass to the stored procedure.

What is the SQL type of the stored procedure parameter strXml?

Greg

"dwg" <dwg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C33B15F9-E08F-49E2-9A5D-67E41D8FD17A@xxxxxxxxxxxxxxxx
In my BizTalk project I create an XML document inside an Orchestration and
drop it to a file using the File Adapter. I also need to send the same XML
document (as XML) to a SQL server database and parse it into tables.
However,
I can not figure out how to properly implement the SQL side.

My schema for the SQL message is <Request><Insert_Order_XML><strXML> with
strXML being an attribute. I figured out how (using a Message Assignment
shape and custom code) to transfer the contents of my original XML
document
into the strXML attribute. The problem is that the resulting message sent
from BizTalk contains "encoded" characters in the strXML attribute. For
example, the "<" charcaters are changed to "<" and the ">" characters are
changed to ">".

What can I do the either stop this encoding from happening or correct it
so
my SQL stored procedures (which use OpenXML to access the data) receive a
properly formatted XML document (as a parameter) to work with?

Or ... is their a better way to approach this?

- DWG





.

Relevant Pages

  • Re: Is there a way to convert ms sql 2008 tables to XML files?
    ... After looking at the code I made it work, since I removed 2 nVARCHARcolumns from the SQL statement where it is used as an RTF. ... I have the 2 XML files the bad and the good which are below: ... Verdana;}{\f1\fnil\fcharset0 Microsoft Sans ... Here is the actual string copied from the ms sql 2008 which works fine; ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Is there a way to convert ms sql 2008 tables to XML files?
    ... If the sql statement returns only several records then I can get the whole XML string with the RTF as well. ... Verdana;}{\f1\fnil\fcharset0 Microsoft Sans ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: XML Message to SQL in Attribute
    ... When you add a string to an Xml node it has to be encoded otherwise you ... The XmlParser that writes the string encodes the string. ... So Biztalk will send an encoded string to SQL and SQL will read and decode ... What is the SQL type of the stored procedure parameter strXml? ...
    (microsoft.public.biztalk.general)
  • Re: need advice
    ... I tried returning a SqldataReader but that failed with: To be XML serializable, types which inherit from IEnumerable must have an implementation of Addat all levels of their inheritance hierarchy. ... I was thinking about getting SQL 2000 to return an XML object but in it's first implementation of XML, ... I do not want to do any data manipulation - only return to the consumer the data from the stored procedure in the quickest way possible. ... I suggest that you determine where the performance problem is located before you try to solve the problem. ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: sqlParameter + IN( ) clause
    ... string and you application will not check it, ... Before passing XML as a parameter to SP, you could check if it is valid XML ... > I tryed using the parameters collection because of SQL Injection (by the ...
    (microsoft.public.dotnet.framework.adonet)