I'm trying to convert my JSON data into a table format in SQL Server Express 2019. Following are my JSON data:
DECLARE @token INT;
DECLARE @ret INT;
DECLARE @url NVARCHAR(MAX);
DECLARE @json AS TABLE(Json_Table NVARCHAR(MAX))
SET @url = 'http://www.test.com/webservice.php?user=DHRWWF&pass=CVernise&key=DFGJRTSrnwieuwn3&format=json'
EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'send'
INSERT into @json (Json_Table) EXEC sp_OAGetProperty @token, 'responseText'
SELECT * FROM @json
SELECT
metadata.[col1],
metadata.[col2],
metadata.[col3],
x1.[col1],
x1.[col2],
metadata.[col4],
metadata.[col5],
x2.[col1],
x2.[col2],
x3.[col1],
x3.[col2],
FROM OPENJSON((SELECT * FROM @json)) -- USE OPENJSON to begin the parse.
-- At the highest level we n parts
WITH (
[Produts] NVARCHAR(MAX) AS JSON
) AS Data
-- Parse the Metadata
CROSS APPLY OPENJSON([Data].[Produts])
WITH(
[col1] NVARCHAR(MAX),
[col2] NVARCHAR(MAX),
[col3] NVARCHAR(MAX),
[col4] NVARCHAR(MAX) AS JSON,
[col5] NVARCHAR(MAX),
[col6] NVARCHAR(MAX),
[col7] NVARCHAR(MAX) AS JSON,
[col8] NVARCHAR(MAX) AS JSON,
) AS metadata
CROSS APPLY OPENJSON([Metadata].[col4])
WITH(
[col1] NVARCHAR(MAX),
[col2] NVARCHAR(MAX)
) AS x1
CROSS APPLY OPENJSON([Metadata].[col7])
WITH(
[col1] NVARCHAR(MAX),
[col2] NVARCHAR(MAX)
) AS x2
CROSS APPLY OPENJSON([Metadata].[col8])
WITH(
[col1] NVARCHAR(MAX),
[col2] NVARCHAR(MAX)
) AS x3
The result is:
metadata.[col1] | metadata.[col2] | metadata.[col3] | x1.[col1] | x1.[col2] | metadata.[col4] | metadata.[col5] | x2.[col1] | x2.[col2] | x3.[col1] | x3.[col2] |
---|---|---|---|---|---|---|---|---|---|---|
AABBCC | BBCCDD | DDEEFF | 1 | 2 | Sistema automático |
ASDVDs | 1 | 2 | 3 | 4 |
And a need this result
metadata.[col1] | metadata.[col2] | metadata.[col3] | x1.[col1] | x1.[col2] | metadata.[col4] | metadata.[col5] | x2.[col1] | x2.[col2] | x3.[col1] | x3.[col2] |
---|---|---|---|---|---|---|---|---|---|---|
AABBCC | BBCCDD | DDEEFF | 1 | 2 | Sistema automático | ASDVDs | 1 | 2 | 3 | 4 |
TY