XML outcome shorter in the Management Studio then expected

  • I have a weird behaviour, which I hope is related to some setting of my SQL Management Studio.

    I use EXpress Edition of MSSQL:

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (64) with Advanced Services (64-bit) on Windows 7 and I need to convert JSON into XML. I have used THE LINK TO THE EXAMPLE [/url]and created all the objects and ran both

    DECLARE @MyHierarchy JSONHierarchy;

    DECLARE @xml XML;

    INSERT INTO @myHierarchy

    select * from parseJSON('{"menu": {

    "id": "file",

    "value": "File",

    "popup": {

    "menuitem": [

    {"value": "New", "onclick": "CreateNewDoc()"},

    {"value": "Open", "onclick": "OpenDoc()"},

    {"value": "Close", "onclick": "CloseDoc()"}

    ]

    }

    }}')

    SELECT dbo.ToXML(@MyHierarchy);

    and

    DECLARE @MyHierarchy JSONHierarchy;

    DECLARE @xml XML;

    INSERT INTO @myHierarchy

    select * from parseJSON('{

    "Person":

    {

    "firstName": "John",

    "lastName": "Smith",

    "age": 25,

    "Address":

    {

    "streetAddress":"21 2nd Street",

    "city":"New York",

    "state":"NY",

    "postalCode":"10021"

    },

    "PhoneNumbers":

    {

    "home":"212 555-1234",

    "fax":"646 555-4567"

    }

    }

    }

    ');

    SELECT dbo.ToXML(@MyHierarchy);

    from the above example.

    In both of the cases SQL Management Studio returned ONLY first 258 characters, whilst running the same

    statements from the Command Line (CMD => OSQL -U -P .....) returned the whole XML, as expected.

    Is it a known issue? Any suggestion, please?

    Many thanks!

  • It's not an issue. It is default setting.

    menu Tools/Options

    expand Query Results/SQL Server

    select Results to Text

    change "maximum number of characters displayed..." to 8192 (that is absolute max to be displayed in SSMS)

    If you ever need to display longer values, you will need to use SUBSTRING function...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Priceless!

    Thank you

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

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