|
Home > Archive > BizTalk Server General > March 2006 > XML Message to SQL in Attribute
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
XML Message to SQL in Attribute
|
|
|
| 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
| |
| Greg Forsythe 2006-03-25, 11:37 am |
| 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@discussions.microsoft.com> wrote in message
news:C33B15F9-E08F-49E2-9A5D-67E41D8FD17A@microsoft.com...
> 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
>
>
>
| |
|
| 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@discussions.microsoft.com> wrote in message
> news:C33B15F9-E08F-49E2-9A5D-67E41D8FD17A@microsoft.com...
>
>
> .
>
|
|
|
|
|