0

I'm parsing data from a JSON File and am having issues when I finally load into my table, the data where there's special characters such as ñ shows as ñ instead.

The code is in NVARCHAR and I'm not sure why it's not pulling correctly

INSERT INTO @recordsInfo
    (
        messageId,
        messageType,
        messageVariation,
        windowEnvelope,
        recipientFirstName,
        recipientLastName,
        recipientAddressLine1,
        recipientAddressLine2,
        recipientCity,
        recipientState,
        recipientZip,
        recipientZipSuffix
    )
    SELECT messageId,
           messageType,
           messageVariation,
           windowEnvelope,
           recipientFirstName,
           recipientLastName,
           recipientAddressLine1,
           recipientAddressLine2,
           recipientCity,
           recipientState,
           recipientZip,
           recipientZipSuffix
    FROM
        OPENJSON(@ValueR)
        WITH
        (
            messageId NVARCHAR(100),
            messageType NVARCHAR(100) '$.messageDetails.messageType',
            messageVariation NVARCHAR(100) '$.messageDetails.messageVariation',
            windowEnvelope NVARCHAR(100) '$.messageDetails.windowEnvelope',
            recipientFirstName NVARCHAR(100) '$."shippingDetails"."recipientFirstName"',
            recipientLastName NVARCHAR(100) '$."shippingDetails"."recipientLastName"',
            recipientAddressLine1 NVARCHAR(1000) '$.shippingDetails.recipientAddressLine1',
            recipientAddressLine2 NVARCHAR(100) '$.shippingDetails.recipientAddressLine2',
            recipientCity NVARCHAR(100) '$.shippingDetails.recipientCity',
            recipientState NVARCHAR(100) '$.shippingDetails.recipientState',
            recipientZip NVARCHAR(100) '$.shippingDetails.recipientZip',
            recipientZipSuffix NVARCHAR(100) '$.shippingDetails.recipientZipSuffix'
        );

DECLARE @recordsInfo TABLE
(
    RecID INT IDENTITY(1, 1),
    messageId NVARCHAR(100),
    messageType NVARCHAR(100),
    messageVariation NVARCHAR(100),
    windowEnvelope NVARCHAR(100),
    recipientFirstName NVARCHAR(100),
    recipientLastName NVARCHAR(100),
    recipientAddressLine1 NVARCHAR(1000),
    recipientAddressLine2 NVARCHAR(100),
    recipientCity NVARCHAR(100),
    recipientState NVARCHAR(100),
    recipientZip NVARCHAR(100),
    recipientZipSuffix NVARCHAR(100)
);

RecipientFirstName is the primary one I'm looking at

3
  • Is the table also NVARCHAR? Commented Jul 9, 2019 at 17:57
  • @JacobH Yes I believe so Commented Jul 9, 2019 at 18:02
  • Can you provide what is assigned to "@ValueR"? Commented Jul 9, 2019 at 21:39

1 Answer 1

1

JSON is usually UTF-8. Only in version 2019 does SQL Server understand UTF-8, so until then, you have to do it manually.

Convert text value in SQL Server from UTF8 to ISO 8859-1

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.