I have to talk to a webservice which accept JSON-messages, but is based on a dynamics princapel. so instead of using fixed defined fields, if wants a dynamics build, based on name\values attribute.
F.E.
{
"Begin": [
{
"name": "sysrscols",
"value": "sysrscols"
},
{
"name": "id",
"value": "3"
},
{
"name": "crdate",
"value": "2013-03-22T15:06:57.220"
}
]
}
Using the FOR JSON option in SQL, I get a result with column names & their values. F.e. Query:
SELECT TOP 1 so.name,so.id,so.crdate From sysobjects so FOR JSON PATH, Root('Begin')
Gives as result:
{
"Begin": [
{
"name": "sysrscols",
"id": 3,
"crdate": "2013-03-22T15:06:57.220"
}
]
}
How can I achieve the first result?
CodePudding user response:
You'll need to unpivot your data here, and then use FOR JSON
. I use a dirty solution here and convert the values to a sql_variant
; I suggest you do not do thus. Instead CONVERT
the values to a (n)varchar
and use the appropriate style codes where needed to get the "format" you want.
I also bring your code into the mid 2000's, as the sys.sysobjects
system view is a compatibility view for SQL Server 2000 (as noted in the documentation). As it's 2021, I felt it long past time you started using the "new" system objects:
SELECT V.name,
V.value
FROM sys.objects so
CROSS APPLY (VALUES(CONVERT(sql_variant,so.name),N'name'),
(CONVERT(sql_variant,so.object_id),N'object_id'),
(CONVERT(sql_variant,so.create_date), N'createdate'))V([value],[name])
WHERE so.[name] = 'SomeView' --This would be the object you want
FOR JSON PATH, ROOT('Begin');