SQL Server 2008中如何将数据转换为json格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34373577/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert data to json format in SQL Server 2008?
提问by alax
I am using SQL Server 2008 and want to convert table data into json format. Can I convert it directly through firing query?
我正在使用 SQL Server 2008 并希望将表数据转换为 json 格式。我可以通过触发查询直接转换它吗？
采纳答案by Jovan MSFT
Built in support for formatting query results is added in SQL Server 2016 and it will be available in Azure Database. In older versions you would need to use CLR or some heavy TSQL like:
SQL Server 2016 中添加了对格式化查询结果的内置支持，它将在 Azure 数据库中可用。在旧版本中，您需要使用 CLR 或一些繁重的 TSQL，例如：
回答by Ahliana
I have created a stored procedure that can take your table and output JSON. You can find it at
我创建了一个存储过程，可以获取您的表并输出 JSON。你可以在
Once you run the stored procedure, you can output your table by making a call like the following:
运行存储过程后，您可以通过如下调用来输出您的表：
EXEC SQLTableOrViewToJSON 'MyTable', 'C:\WhereIStowMyJSON\'
回答by Yury
The above solutions seem unnecessarily complicated; starting with 2008, SQL Server supports the following syntax (answer from DBA StackExchange)
上述解决方案似乎不必要地复杂；从 2008 年开始，SQL Server 支持以下语法（来自DBA StackExchange 的回答）
SELECT * FROM dbo.x FOR JSON AUTO;
The above query returns a single JSON-formatted column that looks like this (assuming dbo.xcontains columns col1~col4of corresponding types):
上面的查询返回一个 JSON 格式的列，如下所示（假设dbo.x包含相应类型的列col1~ col4）：
[{"col1":"val1","col2":"val2","col3":5,"col4":"2019-02-11"}]
More details/options here: https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server
更多细节/选项在这里：https: //docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server
