从JSON读取数据时解析浮点值

I have a collection of data on professional fighters. Each fighter's information looks something like this in JSON:

我收集了有关职业战士的数据。每个战斗机的信息在JSON中看起来像这样:

{
    "id": 1356,
    "name": "Anderson Silva",
    "loc": "Curitiba, Parana",
    "nat": "Brazil",
    "height": 187.96,
    "weight": 83.91,
    "class": "Middleweight"
}

I want to read all of these fighters' data into an SQL database. I use Microsoft SQL Server.

我想将所有这些战士的数据读入SQL数据库。我使用的是Microsoft SQL Server。

I have created a table named "dbo.Fighter", with the following contents:

我创建了一个名为“dbo.Fighter”的表,其中包含以下内容:

[dbo].[Fighter](
    [ID] [int] NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Location] [nvarchar](50) NULL,
    [Nationality] [nvarchar](50) NULL,
    [HeightInCm] [decimal](18, 2) NULL,
    [WeightInKg] [decimal](18, 2) NULL,
    [WeightClass] [nvarchar](50) NULL
)

When I add Anderson Silva's data to the table, his height appears as "18796.00" and his weight as "8391.00". Obviously these are not what I want.

当我将Anderson Silva的数据添加到桌面时,他的身高显示为“18796.00”,他的体重为“8391.00”。显然这些不是我想要的。

What went wrong, and what changes should I make?

出了什么问题,我应该做出哪些改变?

Thanks in advance for your help. Please let me know if my post is insufficiently detailed.

在此先感谢您的帮助。如果我的帖子不够详细,请告诉我。

Edit: Sorry that I did not specify this earlier -- I also tried using "float" instead of "decimal", but the result was unfortunately the same...

编辑:对不起,我之前没有指定 - 我也尝试使用“float”而不是“decimal”,但遗憾的是结果相同......

Edit: I added the data using the following F# code:

编辑:我使用以下F#代码添加了数据:

let testPath = @"G:\Test Fighter Json.txt" //This file contains Anderson Silva's data
let xmlDoc = JsonConvert.DeserializeXmlNode(File.ReadAllText(testPath), "dbo.Fighter")
let testDataset = new DataSet("Test Dataset")
let xmlReader = new XmlNodeReader(xmlDoc)
testDataset.ReadXml(xmlReader) |> ignore

let connectionString = @"Data Source=USER\SQLEXPRESS;Initial Catalog=Sherdog Test;Integrated Security=True"
let connection = new SqlConnection(connectionString)
connection.Open()
let datatables = testDataset.Tables
for datatable in datatables do
    Console.WriteLine("Bulk insert commenced in table" + datatable.TableName)
    let bulk = new SqlBulkCopy(connection)
    bulk.DestinationTableName <- datatable.TableName.Replace('{',' ').Replace('}',' ')
    bulk.WriteToServer(datatable);
    Console.WriteLine("Bulk insert completed in table" + datatable.TableName)

EDIT: I realised that the problem was due to the fact that my Windows system locale was not in English. I have changed it and now everything works well. Sorry for the bother!

编辑:我意识到问题是由于我的Windows系统区域设置不是英文的。我改变了它,现在一切都运作良好。对不起打扰!

2 个解决方案

#1

Check this: https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

请查看:https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

Then, using the dbo.ParseJSON() function, you can do something like this:

然后,使用dbo.ParseJSON()函数,您可以执行以下操作:

DECLARE @JSON nvarchar(MAX)
SET @JSON = '
{
    "id": 1356,
    "name": "Anderson Silva",
    "loc": "Curitiba, Parana",
    "nat": "Brazil",
    "height": 187.96,
    "weight": 83.91,
    "class": "Middleweight"
}'

SELECT 
    MAX(CASE WHEN p.name = 'id' THEN CONVERT(int, stringvalue) END) AS ID,
    MAX(CASE WHEN p.name = 'name' THEN CONVERT(nvarchar(50), stringvalue) END) AS Name,
    MAX(CASE WHEN p.name = 'loc' THEN CONVERT(nvarchar(50), stringvalue) END) AS Location,
    MAX(CASE WHEN p.name = 'nat' THEN CONVERT(nvarchar(50), stringvalue) END) AS Nationality,
    MAX(CASE WHEN p.name = 'height' THEN CONVERT(decimal(18, 2), stringvalue) END) AS HeightInCm,
    MAX(CASE WHEN p.name = 'weight' THEN CONVERT(decimal(18, 2), stringvalue) END) AS WeightInKg,
    MAX(CASE WHEN p.name = 'class' THEN CONVERT(nvarchar(50), stringvalue) END) AS WeightClass
FROM dbo.ParseJSON(@JSON) p

That returns, using the exact data types as in your table, the following:

使用表中的确切数据类型返回以下内容:

ID      Name            Location            Nationality HeightInCm  WeightInKg  WeightClass
1356    Anderson Silva  Curitiba, Parana    Brazil      187.96      83.91       Middleweight

#2

this is the C# code that I have tried and it works fine

这是我尝试的C#代码,它工作正常

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using Newtonsoft.Json;
using System.Xml;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
             String testPath = @"G:\TestJson.txt" ;//This file contains    Anderson Silva's data
         XmlDocument xmlDoc = JsonConvert.DeserializeXmlNode(File.ReadAllText(testPath), "dbo.Fighter");
         DataSet testDataset = new DataSet("Test Dataset");
         XmlNodeReader  xmlReader = new XmlNodeReader(xmlDoc);
         testDataset.ReadXml(xmlReader) ;


         String connectionString = @"Data Source=myserver;Initial Catalog=test;Integrated Security=True";
         SqlConnection connection = new SqlConnection(connectionString);
         connection.Open();
         DataTableCollection datatables = testDataset.Tables;
            foreach (DataTable datatable in datatables) 
            {
                Console.WriteLine("Bulk insert commenced in table" + datatable.TableName);
                SqlBulkCopy bulk = new SqlBulkCopy(connection);
                bulk.DestinationTableName= datatable.TableName.Replace('{',' ').Replace('}',' ');
                bulk.WriteToServer(datatable);
                Console.WriteLine("Bulk insert comped in table" + datatable.TableName);
            }
    }
}
}

My test data as below

我的测试数据如下

{Fighter:[{
"id": 1356,
"name": "Anderson Silva",
"loc": "Curitiba, Parana",
"nat": "Brazil",
"height": 187.96,
"weight": 83.91,
"class": "Middleweight"
},
{
"id": 1358,
"name": "Mike",
"loc": "Curitiba, Parana",
"nat": "USA",
"height": 154.43,
"weight": 56.70,
"class": "Lowleweight"
}]}

#1