在Web服务器的表中存储从web方法返回的Json数据 [英] Storing data of Json, returned from a webmethod, in tables of sql server

问题描述

我从web方法获得了一个巨大的json数据集。有没有人可以帮我将数据存储到sql server的表中？

I got a huge json dataset from a web method. Could any one help me to store the data into sql server's table?

推荐答案

Json数据只是一个文本，你可以存储在任何 NVARCHAR中（MAX）字段。但问题是你打算用它做什么？如果你不想查询它，那就没关系。

但是如果你想在查询操作中查看这些数据，那么你必须考虑几种选择：

1）您可以使用一些自定义函数来处理存储在那里的数据。这是一个很好的方法： https：// www .simple-talk.com / sql / t-sql-programming / consume-json-strings-in-sql-server / [ ^ ]

2）你可以将json转换为xml（参见上面的链接），然后使用sql server xml功能查询它（ http：/ /msdn.microsoft.com/en-us/library/ms189075.aspx [ ^ ]。

3）您可以创建json或bson数据类型作为托管数据类型，并在sql server实例上安装此功能。那么这将是最优雅的一个。如果您实现它，请在这里与我们分享！



只是注意：如果您决定切换到PostgreSQL，那么您已经有了这种数据类型：< a href =http://www.postgresql.org/docs/devel/static/datatype-json.html> http://www.postgresql.org/docs/devel/static/datatype-json.html [ ^ ]



注2：如果你需要处理结构化数据而不是关系数据，你可以考虑切换到 NoSQL文档存储 [ ^ ]实现，其中json / bson是默认方法。

Json data is simply a text, you could store in any NVARCHAR(MAX) field. But the question is what do you intend to do with it? If you don't want to "query" it, this is just ok.
But if you want to look inside this data during a query operation, than you have to consider several alternatives:
1) You can use some custom functions to deal with the data stored there. This is a good approach: https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/[^]
2) You could translate json to xml (see above link), and than use sql server xml features to query it (http://msdn.microsoft.com/en-us/library/ms189075.aspx[^]).
3) You could create json or bson data type as managed data type, and install this feature on sql server instance. Well this would be the most elegant one. If you implement it, please, share it with us here!

Just a note: if you decide to switch to PostgreSQL, there you already have this data type: http://www.postgresql.org/docs/devel/static/datatype-json.html[^]

Note 2: If you have to deal more with structured data, rather than relational one, you might consider switching to a NoSQL Document Store[^] implementation, where json/bson is the default approach.

这篇关于在Web服务器的表中存储从web方法返回的Json数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！