SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,060 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have json where state wise district are exists.
I want the state wise data into row wise107519-my-data.txt
Please help.
Thanks in advance
Hi @sourav dutta ,
You would need SQL Server 2016 or later.
I saved your JSON file as 'e:\Temp\souravdutta-2069.json'.
Here is how to convert it into a rectangular/relational format and load into a DB table.
SQL
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, District VARCHAR(100), City VARCHAR(100));
INSERT INTO @tbl (District, City)
SELECT t.[key] AS District, city.value AS City
FROM OPENROWSET (BULK N'e:\Temp\souravdutta-2069.json'
, SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn) AS t
CROSS APPLY OPENJSON(t.value) AS city;
-- test
SELECT * FROM @tbl;
Partial Output
+----+-----------------------------+-------------+
| ID | District | City |
+----+-----------------------------+-------------+
| 1 | Andaman and Nicobar Islands | Port Blair* |
| 2 | Andhra Pradesh | Adoni |
| 3 | Andhra Pradesh | Amalapuram |
| 4 | Andhra Pradesh | Anakapalle |
| 5 | Andhra Pradesh | Anantapur |
| 6 | Andhra Pradesh | Bapatla |
+----+-----------------------------+-------------+
Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.
@sourav dutta ,
What's the latest on your end?
Did you have a chance to try the proposed solution?