Formatting OpenJSON...WITH
EthanSteiner
Posts: 2 Bronze 1
When checking the formatting (Ctrl-K, Ctrl-Y) it gives me the error "Error laying out SQL" and red-underlines the entire WITH clause for OpenJSON.
Example Code (From MSDN site)
DECLARE @json NVARCHAR(MAX)
SET @json = N'{"Orders":
{"OrdersArray":
[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]
}
}'
SELECT * FROM
OPENJSON ( @json, '$.Orders.OrdersArray' )
WITH (
Number varchar(200) '$.Order.Number',
Date datetime '$.Order.Date',
Customer varchar(200) '$.AccountNumber',
Quantity int '$.Item.Quantity',
[Order] nvarchar(MAX) AS JSON
)
Example Code (From MSDN site)
DECLARE @json NVARCHAR(MAX)
SET @json = N'{"Orders":
{"OrdersArray":
[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]
}
}'
SELECT * FROM
OPENJSON ( @json, '$.Orders.OrdersArray' )
WITH (
Number varchar(200) '$.Order.Number',
Date datetime '$.Order.Date',
Customer varchar(200) '$.AccountNumber',
Quantity int '$.Item.Quantity',
[Order] nvarchar(MAX) AS JSON
)
Comments
What is the full version number of SQL Prompt you are using? You can obtain this information from the SQL Prompt menu ->Help ->About.
What version of SSMS are you using?
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com