Unable to deserialize JSON response

Question

Hi I am Getting an XML response from the API and I am converting that XML response to JSON and then ingesting data in database using C# objects. After converting that XML into JSON and deserializing that JSON is throwing me the error as below.

Newtonsoft.Json.JsonSerializationException: 'Cannot deserialize the current JSON array (e.g. [1,2,3]) into type 'AMZ_All_Orders_Datewise.Program+OrderItem' 
because the type requires a JSON object (e.g. {"name":"value"}) to deserialize correctly.
To fix this error either change the JSON to a JSON object (e.g. {"name":"value"}) or 
change the deserialized type to an array or a type that implements a collection interface 
(e.g. ICollection, IList) like List<T> that can be deserialized from a JSON array. JsonArrayAttribute 
can also be added to the type to force it to deserialize from a JSON array.
Path 'AmazonEnvelope.Message[116].Order.OrderItem', line 1, position 107783.'

The code used for converting XML to JSON is below

//Response from API is stored in xml variable
string xml = Response.Content;
XmlDocument doc = new XmlDocument();
doc.LoadXml(xml);
string Jsontext = JsonConvert.SerializeXmlNode(doc);

XML converted JSON response is stored in Jsontext variable. now the JSON is deserialized using Newtonsoft JSON package

Root_Orders_Data root_Orders_Data = JsonConvert.DeserializeObject<Root_Orders_Data>(Jsontext);

While executing this above line then above error is thrown. Please help me with this. Suggest any idea or any corrections. XML response is Below

<?xml version="1.0" encoding="UTF-8"?>
<AmazonEnvelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="amzn-envelope.xsd">
  <Header>
     <DocumentVersion>1.00</DocumentVersion>
     </Header>
    <MessageType>AllOrdersReport</MessageType>
    <Message>
         <Order>
            <AmazonOrderID>407-4867592-2717133</AmazonOrderID>
            <MerchantOrderID>407-4867592-2717133</MerchantOrderID>
            <PurchaseDate>2021-01-03T18:29:44+00:00</PurchaseDate>
            <LastUpdatedDate>2021-01-05T08:03:11+00:00</LastUpdatedDate>
            <OrderStatus>Shipped</OrderStatus>
            <SalesChannel>Amazon.in</SalesChannel>
            <FulfillmentData>
               <FulfillmentChannel>Amazon</FulfillmentChannel>
               <ShipServiceLevel>Expedited</ShipServiceLevel>
               <Address>
                  <City>PATNA</City>
                  <State>BIHAR</State>
                  <PostalCode>800020</PostalCode>
                  <Country>IN</Country>
               </Address>
           </FulfillmentData>
           <IsBusinessOrder>false</IsBusinessOrder>
           <IsSoldByAB>false</IsSoldByAB>
           <OrderItem>
              <AmazonOrderItemCode>65393459928915</AmazonOrderItemCode>
              <ASIN>B07GMRJTS9</ASIN>
              <SKU>CT4G4DFS8266-01</SKU>
              <ItemStatus>Shipped</ItemStatus>
              <ProductName>Crucial RAM 4GB DDR4 2666 MHz CL19 Desktop Memory CT4G4DFS8266</ProductName>
              <Quantity>1</Quantity>
              <ItemPrice>
                 <Component>
                    <Type>Principal</Type>
                    <Amount currency="INR">1450.0</Amount>
                 </Component>
                 <Component>
                    <Type>Shipping</Type>
                    <Amount currency="INR">40.0</Amount>
                 </Component>
              </ItemPrice>
              <Promotion>
                <PromotionIDs>IN Core Free Shipping 2015/04/08 23-48-5-108</PromotionIDs>
                 <ShipPromotionDiscount>40.0</ShipPromotionDiscount>
              </Promotion>
           </OrderItem>
        </Order>
    </Message>
    <Message>
         <Order>
            <AmazonOrderID>406-0676704-1460352</AmazonOrderID>
            <MerchantOrderID>406-0676704-1460352</MerchantOrderID>
            <PurchaseDate>2021-01-01T17:58:26+00:00</PurchaseDate>
            <LastUpdatedDate>2021-01-02T07:27:17+00:00</LastUpdatedDate>
            <OrderStatus>Shipped</OrderStatus>
            <SalesChannel>Amazon.in</SalesChannel>
            <FulfillmentData>
               <FulfillmentChannel>Amazon</FulfillmentChannel>
               <ShipServiceLevel>Expedited</ShipServiceLevel>
               <Address>
                  <City>BENGALURU</City>
                  <State>KARNATAKA</State>
                  <PostalCode>560051</PostalCode>
                  <Country>IN</Country>
               </Address>
           </FulfillmentData>
           <IsBusinessOrder>false</IsBusinessOrder>
           <IsSoldByAB>false</IsSoldByAB>
           <OrderItem>
              <AmazonOrderItemCode>65883701062139</AmazonOrderItemCode>
              <ASIN>B07Z87LXY1</ASIN>
              <SKU>F4-3600C16D-16GTZRC</SKU>
              <ItemStatus>Shipped</ItemStatus>
              <ProductName>G.Skill F4-3600C16D-16GTZRC Trident Z RGB DDR4-3600MHz CL16-19-19-39 1.35V 16GB (2x8GB) Memory</ProductName>
              <Quantity>1</Quantity>
              <ItemPrice>
                 <Component>
                    <Type>Principal</Type>
                    <Amount currency="INR">11699.0</Amount>
                 </Component>
              </ItemPrice>
           </OrderItem>
           <OrderItem>
              <AmazonOrderItemCode>29991566012307</AmazonOrderItemCode>
              <ASIN>B089XVWVZ9</ASIN>
              <SKU>90MB1490-M0IAY0</SKU>
              <ItemStatus>Shipped</ItemStatus>
              <ProductName>ASUS TUF Gaming B550M-Plus AM4 PCIe 4.0 DDR4 (4600 O.C.) mATX Motherboard with 2.5Gb Ethernet WiFi 6 2X M.2 USB 3.2 Gen2 and Aura Sync RGB Support</ProductName>
              <Quantity>1</Quantity>
              <ItemPrice>
                 <Component>
                    <Type>Principal</Type>
                    <Amount currency="INR">15940.0</Amount>
                 </Component>
              </ItemPrice>
           </OrderItem>
        </Order>
    </Message>
    <Message>
         <Order>
            <AmazonOrderID>171-4651818-8974757</AmazonOrderID>
            <MerchantOrderID>171-4651818-8974757</MerchantOrderID>
            <PurchaseDate>2021-01-01T17:54:10+00:00</PurchaseDate>
            <LastUpdatedDate>2021-01-02T07:26:52+00:00</LastUpdatedDate>
            <OrderStatus>Shipped</OrderStatus>
            <SalesChannel>Amazon.in</SalesChannel>
            <FulfillmentData>
               <FulfillmentChannel>Amazon</FulfillmentChannel>
               <ShipServiceLevel>Expedited</ShipServiceLevel>
               <Address>
                  <City>Anantapur</City>
                  <State>ANDHRA PRADESH</State>
                  <PostalCode>515001</PostalCode>
                  <Country>IN</Country>
               </Address>
           </FulfillmentData>
           <IsBusinessOrder>false</IsBusinessOrder>
           <IsSoldByAB>false</IsSoldByAB>
           <OrderItem>
              <AmazonOrderItemCode>38919417111003</AmazonOrderItemCode>
              <ASIN>B07HY3QWM7</ASIN>
              <SKU>DTSWIVL/16GBIN</SKU>
              <ItemStatus>Shipped</ItemStatus>
              <ProductName>Kingston DataTraveler Swivl 16GB USB 3.0 Pen Drive (DTSWIVL/16GBIN)</ProductName>
              <Quantity>1</Quantity>
              <ItemPrice>
                 <Component>
                    <Type>Principal</Type>
                    <Amount currency="INR">399.0</Amount>
                 </Component>
              </ItemPrice>
           </OrderItem>
        </Order>
    </Message>
</AmazonEnvelope>

C# objects are below

public class Header
        {
            public string DocumentVersion { get; set; }
        }

        public class Address
        {
            public string City { get; set; }
            public string State { get; set; }
            public string PostalCode { get; set; }
            public string Country { get; set; }
        }

        public class FulfillmentData
        {
            public string FulfillmentChannel { get; set; }
            public string ShipServiceLevel { get; set; }
            public Address Address { get; set; }
        }

        public class Amount
        {
            public string _currency { get; set; }
            public string __text { get; set; }
        }

        public class Component
        {
            public string Type { get; set; }
            public Amount Amount { get; set; }
        }

        public class ItemPrice
        {
            public List<Component> Component { get; set; }
        }

        public class Promotion
        {
            public string PromotionIDs { get; set; }
            public string ShipPromotionDiscount { get; set; }
        }

        public class OrderItem
        {
            public string AmazonOrderItemCode { get; set; }
            public string ASIN { get; set; }
            public string SKU { get; set; }
            public string ItemStatus { get; set; }
            public string ProductName { get; set; }
            public string Quantity { get; set; }
            public ItemPrice ItemPrice { get; set; }
            public Promotion Promotion { get; set; }
            public string NumberOfItems { get; set; }
        }

        public class Order
        {
            public string AmazonOrderID { get; set; }
            public string MerchantOrderID { get; set; }
            public DateTime PurchaseDate { get; set; }
            public DateTime LastUpdatedDate { get; set; }
            public string OrderStatus { get; set; }
            public string SalesChannel { get; set; }
            public FulfillmentData FulfillmentData { get; set; }
            public string IsBusinessOrder { get; set; }
            public string IsSoldByAB { get; set; }
            public List<OrderItem> OrderItem { get; set; }
            public string FulfilledBy { get; set; }
        }

        public class Message
        {
            public Order Order { get; set; }
        }

        public class AmazonEnvelope
        {
            public Header Header { get; set; }
            public string MessageType { get; set; }
            public List<Message> Message { get; set; }
            [JsonProperty("_xmlns:xsi")]
            public string XmlnsXsi { get; set; }
            [JsonProperty("_xsi:noNamespaceSchemaLocation")]
            public string XsiNoNamespaceSchemaLocation { get; set; }
        }

        public class Root_Orders_Data
        {
            public AmazonEnvelope AmazonEnvelope { get; set; }
        }

Answer 1

Here is a conceptual example for you.

It covers one-to-many scenario similar to yours for Order and OrderDetails.

SQL

-- DDL and sample data population, start
USE tempdb;
GO

CREATE TABLE #orders (
   OurOrderID INT IDENTITY PRIMARY KEY,
    OrderID      CHAR(5)   NOT NULL,
    CustomerID   CHAR(5)   NOT NULL,
    OrderDate   DATE       NOT NULL,
    EmployeeID   INT        NOT NULL
);
    
CREATE TABLE #details (
   OrderDetailID INT IDENTITY,
   OurOrderID   INT           NOT NULL FOREIGN KEY REFERENCES #orders(OurOrderID),
    ProductID   INT           NOT NULL,
    Price      DECIMAL(10,2) NOT NULL,
    Qty         INT           NOT NULL,
    PRIMARY KEY (OrderDetailID, OurOrderID, ProductID)
);
    
DECLARE @orderidmap TABLE (
   OurOrderID   INT PRIMARY KEY,
    TheirOrderID INT NOT NULL UNIQUE
);
                          
DECLARE @xml XML =
N'<Orders>
    <Order OrderID="13000" CustomerID="ALFKI" OrderDate="2006-09-20Z" EmployeeID="2">
        <OrderDetails ProductID="76" Price="123" Qty="10"/>
        <OrderDetails ProductID="16" Price="3.23" Qty="20"/>
    </Order>
    <Order OrderID="13001" CustomerID="VINET" OrderDate="2006-09-20Z" EmployeeID="1">
        <OrderDetails ProductID="12" Price="12.23" Qty="1"/>
    </Order>
</Orders>';
-- DDL and sample data population, end

/*
Propagate generated IDENTITY values for PRIMARY KEY as FOREIGN KEY in the child table
=============================================================================================
We have an XML document with order data, and there is an order ID in that data. 
To be able to store both header and details, we need a mapping, 
and to this end we use the MERGE statement with the odd condition 1 = 0 
in the USING clause and there is only one branch for WHEN NOT MATCHED. 
We use the OUTPUT clause, and we insert both order IDs into the @orderidmap table.    
*/
;WITH OrderData AS 
(
    SELECT TheirOrderID = c.value('@OrderID[1]',    'INT'),
           CustomerID   = c.value('@CustomerID[1]', 'CHAR(5)'),
           OrderDate    = c.value('@OrderDate[1]',  'DATETIME'),
           EmployeeID   = c.value('@EmployeeID[1]', 'SMALLINT')
    FROM   @xml.nodes('/Orders/Order') AS t(c)
 )
 MERGE #orders AS o
 USING OrderData AS od ON 1 = 0
 WHEN NOT MATCHED THEN
    INSERT(OrderID, CustomerID, OrderDate, EmployeeID)  
       VALUES(od.TheirOrderID, od.CustomerID, od.OrderDate, od.EmployeeID)
 OUTPUT inserted.OurOrderID, od.TheirOrderID 
   INTO @orderidmap (OurOrderID, TheirOrderID);
    
;WITH Details AS 
(
    SELECT TheirOrderID = o.value('@OrderID[1]',    'INT'),
           ProductID    = od.value('@ProductID[1]',   'SMALLINT'),
           Price        = od.value('@Price[1]',     'DECIMAL(10,2)'),
           Qty          = od.value('@Qty[1]',       'INT')
    FROM   @xml.nodes('/Orders/Order') AS A(o)
      CROSS APPLY A.o.nodes('OrderDetails') AS B(od)
) 
INSERT #details (OurOrderID, ProductID, Price, Qty)
SELECT m.OurOrderID, d.ProductID, d.Price, d.Qty
FROM   Details AS d
   INNER JOIN @orderidmap AS m ON d.TheirOrderID = m.TheirOrderID;

-- test    
SELECT * FROM #orders;
SELECT * FROM @orderidmap;
SELECT * FROM #details;
GO
DROP TABLE #orders, #details;