title: How to create a single object JSON array in SQL?
body: <p>I am trying to convert a row of data into a JSON array containing a single object. One column contains XML, which outlines another single object that is converted to an array.</p>
  
  <p>My query:</p>
  
  <pre><code>WITH r AS (
  SELECT TOP 1 * FROM Table1
  ORDER BY RecordID ASC)
  
  SELECT 
  NEWID() AS 'Report.ReportUUID', 
  Value1 as 'Report.Value1', 
  Value2 as 'Report.Value2',
  DateTime as 'Report.DateTime',
  UserID as 'Report.UserID',
  'Medium' as 'Report.Priority',
  NEWID() as 'Report.Item.ItemUUID',
  XML.value('category[1]', 'varchar(100)') as 'Report.Item.Category',
  XML.value('description[1]', 'varchar(1000)') as 'Report.Item.Description',
  XML.value('date[1]', 'varchar(100)') AS 'Report.Item.DateTime'
  FROM r
  FOR JSON PATH, ROOT('DataSet');
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>{
    "DataSet" : {
      "Report" : [
        {
          "ReportUUID" : "uuid here",
          "Value1" : "value1",
          "Value2" : "value2",
          "DateTime" : "2020-04-06 16:00:00",
          "UserID" : "1234",
          "Priority" : "Medium",
          "Item" : [
            {
              "ItemUUID" : "uuid here",
              "Category" : "01",
              "Description" : "Desc",
              "DateTime" : "2020-04-05 08:00:00"
            }
          ]
        }
      ]
    }
  }
  </code></pre>
  
  <p>Actual output:</p>
  
  <pre><code>{
      "DataSet": [
          {
              "Report": {
                  "ReportUUID" : "uuid here",
                  "Value1": "value1",
                  "Value2": "value2",
                  "DateTime": "2020-04-06 16:00:00",
                  "UserID": "1234",
                  "Priority": "Medium",
                  "Item": {
                      "ItemUUID": "uuid here",
                      "Category": "01",
                      "Description": "Desc",
                      "DateTime": "2020-04-05 08:00:00"
                  }
              }
          }
      ]
  }
  </code></pre>
  
  <p><code>DataSet</code> needs to be a single object, and <code>Report</code> and <code>Item</code> have to be arrays containing a single object. Can anyone help with formatting the data like this?</p>