Post reply

XML Limitations - Does FOR XML has any limitation on number of nodes/elements/attributes

  • December 2, 2009 at 10:02 am

    #213146

    I have a problem with SPs which were working fine but now due to larger xml data output, it is showing empty row in SSMS but returning xml to application. Same is the case with following two scenarios,

    Scenario 1:

    --1.

    select cast(('<root>'+REPLICATE(cast ('<abc/>' as varchar(max)), 100000) + '</root>') as XML) ......... is returning an empty row

    --2.

    select cast(('<root>'+REPLICATE(cast ('<abc/>' as varchar(max)), 1000) + '</root>') as XML) is returning valid xml

    Scenario 2:

    --1:

    select substring(('<root>'+REPLICATE(cast ('<abc/>' as varchar(max)), 100000) + '</root>'), 1, 43680) -- empty cell in a grid

    --2:

    select substring(('<root>'+REPLICATE(cast ('<abc/>' as varchar(max)), 100000) + '</root>'), 1, 43679) -- returns valid string

    Any reason/suggestion please ?

    VERSION:

    9.00.4053.00

    SP3

    Developer Edition (64-bit)

    3

    Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )

  • December 3, 2009 at 6:15 am

    #1087022

    Sounds like your SSMS settings need to be tweaked. I was able to run all 4 queries and brought back data. Look in the menu under Tools.Options. In there, there is an option for Query Results.SQL Server, Results To Grid. In the Maximum Characters Retrieved section, I have 65535 for Non XML and 2Mb for XML. See if that works

    For better, quicker answers, click on the following...
    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/

  • December 4, 2009 at 3:25 am

    #1087559

    My SSMS settings are

    NON-XML = 65535

    XML = Unlimited (and even tried with 2MB)

    still getting empty row for these two:

    select cast(('<root>'+REPLICATE(cast ('<abc/>' as varchar(max)), 100000) + '</root>') as XML)

    select substring(('<root>'+REPLICATE(cast ('<abc/>' as varchar(max)), 100000) + '</root>'), 1, 43680) -- empty cell in a grid

    Really unable to understand the reason. I have already checked for windows updates and applied all. See the version details above.

    Anything else in your mind ?

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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