SQL Server – Storing linebreaks in XML data type

Is it possible to store non-alphanumeric characters (more specifically line break characters) in a XML data type?

The code below illustrates my problem:

  • SQL Join on Nearest less than date
  • Order By month and year in sql with sum
  • Sql Server compact database with linq-to-sql in visual studio 2010
  • How to profile for one table in SQL Server?
  • select the TOP N rows from a table
  • Unable to connect to MS SQL Server using Go
  • declare @a xml
    declare @b nvarchar(max)
    set @b = '<Entry Attrib="1'+CHAR(13)+'2" />'
    print @b
    set @a=convert(xml,@b,1)
    set @b=convert(nvarchar, @a,1)
    print @b

    The output is:

    <Entry Attrib="1
    2" />
    <Entry Attrib="1 2"/>

    Is there any way I could keep the line break intact?

    My actual problem is to store the value in a table (rather than a local variable), so maybe there’s some setting for the corresponding XML column in my table that would do the job?

    3 Solutions collect form web for “SQL Server – Storing linebreaks in XML data type”

    It would not be possible. The XML Data type is stored as an XML DOM Tree, not a string.

    You would have to store it as a varchar instead if you want to keep whitespace.

    My answer in the XSLT context should apply here:

    XML parsed entities are often stored
    in computer files which, for editing
    convenience, are organized into lines.
    These lines are typically separated by
    some combination of the characters
    carriage-return (#xD) and line-feed

    So this might be what you are looking for:

    set @b = '<Entry Attrib="1&#xA;2" />'

    White space inside of an XML tag is not considered significant according to the XML specification, and will not be preserved. White space outside of the element will however:

    declare @a xml
    declare @b nvarchar(max)
    set @b = '<Entry Attrib="12"> fo'+CHAR(13)+'o</Entry>'
    print @b
    set @a=convert(xml,@b,1)
    set @b=convert(nvarchar(max), @a,1)
    print @b

    will output:

    <Entry Attrib="12"> fo
    <Entry Attrib="12"> fo
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.