locked
What is 
 and how can I remove it? RRS feed

  • Question

  •  

    I'm selecting a batch of data from SQL Server using "FOR XML AUTO, ELEMENTS, ROOT()" in a stored procedure.

     

    I'm then using VB.Net in Visual Studio 2005 to run the stored procedure and read the data into a SqlDataReader.

     

    The data in the database doesn't contain this "
" string, but it is present once read into .Net. I'm aware that it is some form of encoding and it is a carriage-return or something.

     

    What I don't understand is why it is necessary?

     

    Also, is there anyway of removing it?

     

    I also have "", "" and "". Presumably, there is a way to prevent these being encoded.

     

    Many Thanks

     

     

    Rich

    Tuesday, January 29, 2008 3:44 PM

Answers

  • by using TYPE, you can prevent the encoding of some of those. for example:

     

    Code Snippet

    declare @t table (fld varchar(50))

    insert into @t (fld) select 'Carriage return

    tab '

     

    select * from @t t for xml auto, elements, root('test')

    /*

    <test><t><fld>Carriage return&#x0D;

    tab </fld></t></test>

    */

     

    select * from @t t for xml auto, elements, root('test'), TYPE

    /*

    <test><t><fld>Carriage return

    tab </fld></t></test>

    */

     

     

    Tuesday, January 29, 2008 4:18 PM
  • Hi Rich

     

    &#x0D; is the character entity for carriage return. Any XML parser should recognize it and turn it back into a carriage return. If that is not happening in .Net, you should talk to their team. The other workaround with using the type directive may help to work around the issue.

     

    However the others "&#x0b;", "&#x01;" and "&#x03;" are not valid XML 1.0 characters according to the XML 1.0 spec. So the TYPE directive will raise an error on them. As will all conformant XML 1.0 parsers (some older MSXML parsers allowed you to parse them anyway).

     

    To address them, you should clean your data first and remove the xB, x1 etc characters from your data.

     

    I hope this helps

    Michael 

    Tuesday, January 29, 2008 9:15 PM

All replies

  • Well, x0D is the hexadecimal representation for a carriage return character and the others look binary representations of excape characters and there is a better-than-average chance that these did in fact come from your input string; are you sure you want to remove these?

     

    Tuesday, January 29, 2008 4:14 PM
  • by using TYPE, you can prevent the encoding of some of those. for example:

     

    Code Snippet

    declare @t table (fld varchar(50))

    insert into @t (fld) select 'Carriage return

    tab '

     

    select * from @t t for xml auto, elements, root('test')

    /*

    <test><t><fld>Carriage return&#x0D;

    tab </fld></t></test>

    */

     

    select * from @t t for xml auto, elements, root('test'), TYPE

    /*

    <test><t><fld>Carriage return

    tab </fld></t></test>

    */

     

     

    Tuesday, January 29, 2008 4:18 PM
  • Hi Rich

     

    &#x0D; is the character entity for carriage return. Any XML parser should recognize it and turn it back into a carriage return. If that is not happening in .Net, you should talk to their team. The other workaround with using the type directive may help to work around the issue.

     

    However the others "&#x0b;", "&#x01;" and "&#x03;" are not valid XML 1.0 characters according to the XML 1.0 spec. So the TYPE directive will raise an error on them. As will all conformant XML 1.0 parsers (some older MSXML parsers allowed you to parse them anyway).

     

    To address them, you should clean your data first and remove the xB, x1 etc characters from your data.

     

    I hope this helps

    Michael 

    Tuesday, January 29, 2008 9:15 PM
  •  

    Thanks for the good answers folks.

     

    So, it would seem that these 'things' are within my data, just that when I query the data in SQL Server Management Studio they are displayed in a more desirable manner.

     

    Cleaning the data certainly sounds like a good idea. I will investigate that.

     

    As for the &#x0D; - I wonder if my problem is that I am reading the data into a String object, whereas I might be better using some form of XML object.

     

    It's proving difficult to ask the appropriate questions, because this is a subject which I have very little experience of. I appreciate the clear answers so far.

    Wednesday, January 30, 2008 9:32 AM
  • Probably you can show a bit of the .NET code that you are using so that some one can suggest a fix.

    • Proposed as answer by RMNathan Wednesday, August 18, 2010 11:01 AM
    Thursday, January 31, 2008 5:22 AM
  • Probably you can show a bit of the .NET code that you are using so that some one can suggest a fix.


    sorry by mistake marked this as an answer
    Wednesday, August 18, 2010 11:03 AM