Convert JSON to SQL Format

Question

Hello Community,

I have the attached JSON Code. I would like to be able recode the JSON so as to able to work with the code in SQL.

When you look at the file, you will see that it is very large, so I apologise in advance. However, if someone could either help convert the code, or put on the path to be able to do it myself I would be most grateful.

Thanks171006-json-code.txt

Answer 1

Hi @Carlton Patterson ,

Please try the following solution.
It will give you a good jump start.

SQL

SELECT report.*  
FROM OPENROWSET(BULK N'e:\Temp\json-code.json', SINGLE_CLOB) AS j  
CROSS APPLY  
OPENJSON(BulkColumn, N'$.mappings[0].ELLIPSE.columns')  
WITH   
(  
    [column]		VARCHAR(100)	'$.column'  
    , [type]		VARCHAR(20)		'$.type'  
    , [length]		INT				'$.length'  
    , [allowNulls]	VARCHAR(5)		'$.allowNulls'   
    , [transform]	VARCHAR(200)	'$.transform'  
    , [mapType]		VARCHAR(20)		'$.mapType'  
    , [source]		NVARCHAR(MAX) AS JSON  
) AS report;  

Answer 2

YitzhakKhabinsky-0887

I have the tables and columns that I would like to extract, see attached. Ideally, I would like the tables to extracted as individual CSVs.

This request might need someone experienced with Excel - as I can't imagine someone actually copying and pasting the tables and columns into individual csv tables. For example, there is a table called AZ_FND_MSF010, which has multiple columns with each column having its own attributes e.g. DataType. I would need tables and column(with attributes) extracted into a single CSV's

Is that something you (or anyone in the community)171085-newtable.txt could help me with?

Answer 3

Hi @Carlton Patterson ,

> Ideally, I would like the tables to extracted as individual CSVs.

You can use SSMS Import and Export Wizard to export SQL server data to .CSV. Refer to this blog to get more.

Please also check this blog to get other methods to import SQL data to Excel.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".