Post reply

FOR XML PATH('') wierd results?

  • February 11, 2009 at 8:46 am

    #130810

    Hi all,

    OK I've been using this code for sometime in different places for concatenation columns together.

    DECLARE @tbl TABLE

    (id INT IDENTITY(1,1),

    Col VARCHAR(10))

    INSERT INTO @tbl

    SELECT 'a' UNION ALL

    SELECT 'a '

    ;WITH MyCTE (MyField)

    as(SELECT Col + ', ' FROM @tbl FOR XML PATH(''))

    SELECT LEN(MyField) as [len],MyField FROM MyCTE

    today I found two strange things happen.

    If I run the above code on our one server I get results

    Len |Myfield|

    ----------- |

    7 |a, a , |

    On another server I get

    Len |Myfield|

    -----------|

    6 |a, a ,|

    what server setting would cause auto RTRIM?

    The second is that when I use the FOR XML PATH('') some chars get changed e.g.

    DECLARE @tbl TABLE

    (id INT IDENTITY(1,1),

    Col VARCHAR(10))

    INSERT INTO @tbl

    SELECT 'a & b' UNION ALL

    SELECT 'c & d'

    ;WITH MyCTE (MyField)

    as(SELECT Col + ', ' FROM @tbl FOR XML PATH(''))

    SELECT MyField FROM MyCTE

    The '&' symbol gets changed to '&'

    Why is this and is there a list of other chars that this may happen with ?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • February 11, 2009 at 10:37 am

    #942347

    My initial shot in the dark. You have different collations on those servers/databases.


    * Noel

  • February 12, 2009 at 2:04 am

    #942655

    I'll have a look at the collation and get back you .

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • February 12, 2009 at 2:07 am

    #942656

    the Collations on both servers and both db's are the same "SQL_Latin1_general_CP1_CI_AS"

    🙁

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • February 12, 2009 at 3:11 am

    #942667

    Regarding the second question – This is because of the way that XML works. In any XML document (regardless if it was created by SQL Server or by another tool), when you use ampersand, you need to use an escape char (just like you need to use an escape char in SQL when you use the like operator and the string that you are looking for has percent sign in it). The escape char is amp;.

    Regarding the first question – My guess is that in your query you used real data from real table. Could it be that the data was different (e.g. in one table you did have space and in another one you didn’t have the space)?

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 12, 2009 at 3:30 am

    #942676

    Thanks for the answer to the second question 🙂

    With regards to the first question.

    The space that is being trimmed or not being trimmed is from the Concatenation

    ', ' part of the query.

    Just seems strange that it behaves differently on different servers?

    I'm trying to find out which setting could affect this...

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • February 12, 2009 at 5:10 am

    #942729

    I admit that I don’t know of a setting option that changes this behavior, but I would compare the setting options for both sessions and see if there is any difference.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 12, 2009 at 6:28 am

    #942778

    thanks everyone for the help. I'm going to move the first issue to a new topic as I don't think it's related to FOR XML PATH('') so the heading is miss leading. Here is the link to the new

    topic:

    New Topic

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply