OPENJSON unable to parse Portuguese characters

Question

Post reply

OPENJSON unable to parse Portuguese characters

HugoP

SSC Veteran

Points: 269

April 17, 2022 at 11:33 pm

#4017700

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:

Screenshot_26

And a need this result:

Screenshot_27

TY

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.[col5],
metadata.[col6],
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

This topic was modified 2 years, 7 months ago by HugoP.
This topic was modified 2 years, 7 months ago by HugoP.
This topic was modified 2 years, 7 months ago by HugoP.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Can you provide some literal sample content for @json, please. For many people, sp_OACreate and other OA modules are blocked for security reasons.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

HugoP SSC Veteran Points: 269 More actions · Answer 2

thank you Phil Parkin. Follow some content. I hope it's enough for you to help me.

{
  "Produtos": [
    {
      "col1": "AABBCC",
      "col2": "BBCCDD",
      "col3": "DDEEFF",
      "col4": {
        "col1": "1",
        "col2": "2"
      },
      "col5": "Sistema de enrolar autom&aacute;tico:
",
      "col6": "ASDVDs",
      "col7": {
        "col1": "1",
        "col2": "2"
      },
      "col8": {
        "col1": "1",
        "col2": "2"
      }
    }
  ]
}

HugoP SSC Veteran Points: 269 More actions · Answer 3

thank you Phil Parkin. Follow some content. I hope it's enough for you to help me.

{
  "Produtos": [
    {
      "col1": "AABBCC",
      "col2": "BBCCDD",
      "col3": "DDEEFF",
      "col4": {
        "col1": "1",
        "col2": "2"
      },
      "col5": "Sistema de enrolar autom&aacute;tico:
",
      "col6": "ASDVDs",
      "col7": {
        "col1": "1",
        "col2": "2"
      },
      "col8": {
        "col1": "1",
        "col2": "2"
      }
    }
  ]
}

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

OK, in my opinion, the problem does not lie with OPENJSON. The following is valid JSON:

{
  "Produtos": [
    {
      "col1": "AABBCC",
      "col2": "BBCCDD",
      "col3": "DDEEFF",
      "col4": {
        "col1": "1",
        "col2": "2"
      },
      "col5": "Sistema de enrolar automático:",
      "col6": "ASDVDs",
      "col7": {
        "col1": "1",
        "col2": "2"
      },
      "col8": {
        "col1": "1",
        "col2": "2"
      }
    }
  ]
}

You can see that the extended character appears properly within the JSON string.

Therefore it looks like the sp_OAGetProperty proc needs to be investigated further to see whether it can be coaxed into returning un-doctored results.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin