Instead of creating a whole new project, I just modified my SQL query.
SELECT top 2 RTRIM(V.VENDORID) AS VENDORID,
RTRIM(V.VENDNAME) AS VENDNAME,
RTRIM(V.ADDRESS1) AS ADDRESS1,
RTRIM(V.ADDRESS2) AS ADDRESS2,
RTRIM(V.ADDRESS3) AS ADDRESS3,
RTRIM(V.CITY) AS CITY,
RTRIM(V.STATE) AS STATE,
RTRIM(V.ZIPCODE) AS ZIPCODE,
RTRIM(V.VENDSTTS) AS VENDSTTS
FROM PM00200 V
WHERE VENDSTTS = '1'
FOR JSON PATH, ROOT('VENDORS')
I included the ‘top 2’ so as to not make a 500 page blog entry. When executed, I got the following result set:
{"VENDORS":[{"VENDORID":"ACETRAVE0001","VENDNAME":"A Travel Company","ADDRESS1":"123 Riley Street","ADDRESS2":"","ADDRESS3":"","CITY":"Sydney","STATE":"NSW","ZIPCODE":"2086","VENDSTTS":"1"},{"VENDORID":"ADVANCED0001","VENDNAME":"Advanced Office Systems","ADDRESS1":"678 Sherwood Park South","ADDRESS2":"","ADDRESS3":"","CITY":"Arlington Heights","STATE":"IL","ZIPCODE":"60004-2922","VENDSTTS":"1"}]}
If you paste this into a JSON formatter, you get:
{
"VENDORS":[
{
"VENDORID":"ACETRAVE0001",
"VENDNAME":"A Travel Company",
"ADDRESS1":"123 Riley Street",
"ADDRESS2":"",
"ADDRESS3":"",
"CITY":"Sydney",
"STATE":"NSW",
"ZIPCODE":"2086",
"VENDSTTS":"1"
},
{
"VENDORID":"ADVANCED0001",
"VENDNAME":"Advanced Office Systems",
"ADDRESS1":"678 Sherwood Park South",
"ADDRESS2":"",
"ADDRESS3":"",
"CITY":"Arlington Heights",
"STATE":"IL",
"ZIPCODE":"60004-2922",
"VENDSTTS":"1"
}
]
}
Many thanks again to AscendLearning who just made my life a whole lot simpler. Just in time as my weekend begins as soon as I post this.
REMEMBER: You must be using SQL 2016 or better.