Competition: What’s your favorite Redgate tool? Enter now.

Formatting OpenJSON...WITH

EthanSteinerEthanSteiner Posts: 2 Bronze 1
edited January 30, 2017 11:18AM in SQL Prompt
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
)

Comments

  • Eddie DEddie D Posts: 1,688 Rose Gold 5
    Hi, thank you for your forum post and sorry that you have encountered a problem.

    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
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
Sign In or Register to comment.