none
Handling special char at BizTalk end? RRS feed

  • Question

  • I am receiving message as flat file which may contain special char like <> ' "....... etc

    Can any one tell me how can i handle it at BizTalk end.

    If i am converting it into xml due to data, it is not a valid xml.

    <FirstName> <John miller</FirstName>

    And single quotes ' can create problem in sql query .

    Can any one let me know how could i handle it.

    Thursday, February 26, 2015 1:55 AM

Answers

  • Hi Phill,

    Let’s be clear when you mean by “special character” in flat-file schema.

    If the special character which your referring are not the delimiters used in your schema or any escape characters (about escape characters I’ll refer later), then as la Cour said, XML shall handle them without any issues. If you have these characters, as you highlighted, you will have XML file/element like “<FirstName> <John miller</FirstName>”. It’s still a valid XML file.

    But if you don’t want this characters to be saved into SQL database i.e for the above XML element if don’t want the character “<” saved along with “John miller” as “<John miller”, then you need to have a custom pipeline component to remove those characters.

    Escape character:

    If you know that a character which could come in an element, then you can use “Escape Character” property for the element where you can specify the character you want to ignore. i.e of you expect character “<” to come with FirstName element, then you can specify the escape character property as “<” in the “FirstName” element.

    More special character to ignore:

    As said XML can parse these characters without issue. But if you don’t want to send those characters to your destination system (SQL), then after parsing the flat-file into XML, use a custom pipeline component to replace all the special character from the parsed XML.

    Following article discuss about this concept:

    How to remove invalid character in incoming XML message using custom pipeline component

     

     


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Angie Xu Tuesday, March 3, 2015 11:14 AM
    Thursday, February 26, 2015 9:31 AM
  • You really don't need to do anything.

    The Flat File Disassembler will property escape any Xml reserved character automatically.  You example would appear as:

    <FirstName> &lt;John miller</FirstName>

    The single quote is a completely different issue and it does note require escaping in Xml.  In terms of SQL, that would only be a problem if you are hand crafting SQL statements using string concatenation.

    All the BizTalk parts, including the SQL Adapters, use SQL Client Objects, Command, Parameter, etc. so the single quote, or any other character is not an issue.

    • Marked as answer by Angie Xu Tuesday, March 3, 2015 11:14 AM
    Thursday, February 26, 2015 2:05 PM
    Moderator

All replies

  • Create a pipeline component that would translate the special characters. Then u can disassemble

    Regards &lt;br/&gt; When you see answers and helpful posts,&lt;br/&gt; please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Thursday, February 26, 2015 7:14 AM
    Answerer
  • Have you actually tried this and gotten the above problems?

    It is my understanding that both the Flat File Disassembler Pipeline Components and the SQL Adapters will automatically handle all special characters for you, so that you don't have to anything yourself.

    Morten la Cour


    • Edited by la Cour Thursday, February 26, 2015 7:19 AM
    Thursday, February 26, 2015 7:17 AM
  • No I have not come across the scenario, but I have used charater translator in pipeline to replace special characters from flat file.

    Regards &lt;br/&gt; When you see answers and helpful posts,&lt;br/&gt; please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Thursday, February 26, 2015 7:23 AM
    Answerer
  • Hi Phill,

    Let’s be clear when you mean by “special character” in flat-file schema.

    If the special character which your referring are not the delimiters used in your schema or any escape characters (about escape characters I’ll refer later), then as la Cour said, XML shall handle them without any issues. If you have these characters, as you highlighted, you will have XML file/element like “<FirstName> <John miller</FirstName>”. It’s still a valid XML file.

    But if you don’t want this characters to be saved into SQL database i.e for the above XML element if don’t want the character “<” saved along with “John miller” as “<John miller”, then you need to have a custom pipeline component to remove those characters.

    Escape character:

    If you know that a character which could come in an element, then you can use “Escape Character” property for the element where you can specify the character you want to ignore. i.e of you expect character “<” to come with FirstName element, then you can specify the escape character property as “<” in the “FirstName” element.

    More special character to ignore:

    As said XML can parse these characters without issue. But if you don’t want to send those characters to your destination system (SQL), then after parsing the flat-file into XML, use a custom pipeline component to replace all the special character from the parsed XML.

    Following article discuss about this concept:

    How to remove invalid character in incoming XML message using custom pipeline component

     

     


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Angie Xu Tuesday, March 3, 2015 11:14 AM
    Thursday, February 26, 2015 9:31 AM
  • You really don't need to do anything.

    The Flat File Disassembler will property escape any Xml reserved character automatically.  You example would appear as:

    <FirstName> &lt;John miller</FirstName>

    The single quote is a completely different issue and it does note require escaping in Xml.  In terms of SQL, that would only be a problem if you are hand crafting SQL statements using string concatenation.

    All the BizTalk parts, including the SQL Adapters, use SQL Client Objects, Command, Parameter, etc. so the single quote, or any other character is not an issue.

    • Marked as answer by Angie Xu Tuesday, March 3, 2015 11:14 AM
    Thursday, February 26, 2015 2:05 PM
    Moderator