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; }
}
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;