troubleshooting Question

Import Json file into MS SQL

Avatar of Stevie Zakhour
Stevie Zakhour asked on
JSONSQL* import
7 Comments1 Solution110 ViewsLast Modified:
Hi Guys

Trying to import a json formatted file into sql, the query is below

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\szakhour\Desktop\LastScore.json', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON)
WITH 
(
    LeaderboardName varchar(20), 
    LeaderboardNameFull varchar(20), 
    PlayerCount varchar(20), 
    PlayerName varchar(20), 
    Score nvarchar(20), 
    ScoreDetails varchar(10)
)
CROSS APPLY OPENJSON (ScoreDetails) 
WITH(
		ZombiesKilled varchar(15)'$.ZombiesKilled',
		Accuracy varchar(50)'$.Accuracy',
		DamageTaken varchar(20)'$.DamageTaken',
		SecondsInLevel varchar(30)'$.SecondsInLevel',
		Deaths varchar(50)'$.Deaths' --AS JSON
) 

The json formatted file is below

{
  "LeaderboardName": "Missile base",
  "LeaderboardNameFull": "Missile base four players",
  "PlayerCount": 4,
  "PlayerName": "PLAYER",
  "Score": 35360,
  "ScoreDetails": {
    "ZombiesKilled": "335",
    "Accuracy": "31.32",
    "DamageTaken": "100",
    "SecondsInLevel": "1273",
    "Deaths": "1"
  }
}

When I run the query I get no errors however there is no data. See attached png file called Snip20200203_3.Png, this will show you the result after running the query in MS SQL 2016

Any help is greatly appreciated.
Snip20200203_3.png
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros