Re: XML Message to SQL in Attribute

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Greg. After reading your response I did some quick checking and discovered
(through some other posts) that I should have been using ntext instead of
varchar. After updating the SQL SP paramater to ntext everything started
working !!!

Thanks for the nudge :)

- DWG

"Greg Forsythe" wrote:

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
& 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
    ... You are on the right track, with sending Xml to a SQL stored procedure. ... The XmlParser that writes the string encodes the string. ...
    (microsoft.public.biztalk.general)
  • 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)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)