在这种情况下,我非常欣赏XML在处理通用集合方面的强大能力:
SELECT STUFF(b.query('
for $element in ./*
return
<x>;{$element/text()}</x>
').value('.','nvarchar(max)'),1,1,'')
FROM
(
SELECT TOP 3 * FROM sys.objects o FOR XML PATH('row'),ELEMENTS XSINIL,TYPE
) A(a)
CROSS APPLY a.nodes('/row') B(b);
结果
sysrscols;3;4;0;S ;SYSTEM_TABLE;2017-08-22T19:38:02.860;2017-08-22T19:38:02.867;1;0;0
sysrowsets;5;4;0;S ;SYSTEM_TABLE;2009-04-13T12:59:05.513;2017-08-22T19:38:03.197;1;0;0
sysclones;6;4;0;S ;SYSTEM_TABLE;2017-08-22T19:38:03.113;2017-08-22T19:38:03.120;1;0;0
备注
一些需要提及的事项
- 我使用分号
;
作为分隔符,因为连字符-
可能会在包含连字符的值(例如DATE)中出现错误。
- 我使用
sys.objects
中的TOP 3
创建一个易于使用的样例。
- 感谢Zohard Peled,我添加了
ELEMENTS XSINIL
来强制引擎不忽略NULL值。
在2016年之前的版本中创建JSON
您可以尝试使用以下方法在2016年之前的版本中创建JSON字符串
SELECT '{'
+ STUFF(b.query('
for $element in ./*
return
<x>,"{local-name($element)}":"{$element/text()}"</x>
').value('.','nvarchar(max)'),1,1,'')
+ '}'
FROM
(
SELECT TOP 3 * FROM sys.objects o FOR XML PATH('row'),TYPE
) A(a)
CROSS APPLY a.nodes('/row') B(b);
结果
{"name":"sysrscols","object_id":"3","schema_id":"4","parent_object_id":"0","type":"S ","type_desc":"SYSTEM_TABLE","create_date":"2017-08-22T19:38:02.860","modify_date":"2017-08-22T19:38:02.867","is_ms_shipped":"1","is_published":"0","is_schema_published":"0"}
{"name":"sysrowsets","object_id":"5","schema_id":"4","parent_object_id":"0","type":"S ","type_desc":"SYSTEM_TABLE","create_date":"2009-04-13T12:59:05.513","modify_date":"2017-08-22T19:38:03.197","is_ms_shipped":"1","is_published":"0","is_schema_published":"0"}
{"name":"sysclones","object_id":"6","schema_id":"4","parent_object_id":"0","type":"S ","type_desc":"SYSTEM_TABLE","create_date":"2017-08-22T19:38:03.113","modify_date":"2017-08-22T19:38:03.120","is_ms_shipped":"1","is_published":"0","is_schema_published":"0"}
提示
您可以在此查询中添加ELEMENTS XSINIL
。这取决于您是否希望忽略NULL,或者您希望将它们包含在"SomeColumn":""
中。