Here is my code:
DECLARE @Orders VARCHAR(MAX)
select @Orders = BulkColumn FROM OPENROWSET(BULK'C:\temp\amazonjson.json.txt', SINGLE_BLOB) JSON;
SELECT *
FROM OPENJSON(@Orders,'$.orders')
WITH(
orderId bigint '$.orderId',
orderNumber nvarchar(50) '$.orderNumber',
orderKey nvarchar(50) '$.orderKey',
orderDate nvarchar(50) '$.orderDate',
paymentDate nvarchar(50) '$.paymentDate',
shipByDate nvarchar(50) '$.shipByDate',
orderStatus nvarchar(50),
customerId nvarchar(50),
customerUsername nvarchar(50),
customerEmail nvarchar(50),
orderTotal nvarchar(50),
amountPaid nvarchar(50),
taxAmount nvarchar(50),
shippingAmount nvarchar(50),
customerNotes nvarchar(50),
internalNotes nvarchar(50),
gift nvarchar(50),
giftMessage nvarchar(50),
paymentMethod nvarchar(50),
requestedShippingService nvarchar(50),
carrierCode nvarchar(50),
serviceCode nvarchar(50),
packageCode nvarchar(50),
confirmation nvarchar(50),
shipDate nvarchar(50),
holdUntilDate nvarchar(50),
billTo nvarchar(max) '$.billTo',
items nvarchar(max)
)
The issue is the last two columns -- billTo is an object -- {"name":"bob smith","address":"1234 main"} -- and items is an array. I was trying to simply display the column as a json string so I can parse it later.
