November 30, 2012 at 7:54 am
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!
November 30, 2012 at 8:09 am
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...
November 30, 2012 at 8:12 am
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