Skip to main content

Return to Question

added 568 characters in body
Source Link
Crowning
Crowning
  • 167
  • 1
  • 2
  • 11
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)
);

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'
        );

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)
);
Source Link
Crowning
Crowning
  • 167
  • 1
  • 2
  • 11

Issues retrieving special characters from OPENJSON SQL Server

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'
        );

RecipientFirstName is the primary one I'm looking at