OPENJSON unable to parse Portuguese characters

103 views Asked by At

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&aacute;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

0

There are 0 answers