Upload a SharePoint Sandbox solution to Office 365

A Windows SharePoint (WSP) solution is a cab file that enables us to deploy data lists and web content like JavaScript, CSS, ASPX assets to a SharePoint site. It is a sandbox solution because it only enables us to deploy assets that can run on the browsers. When deploying to Office 365, we need to deploy the WSP file to a site that has the solutions feature available. In this article, we take a look at creating a developer site, uploading a solution and then enabling the feature that contains the assets that we need to deploy.

Creating a Developer Site

To create a developer site, we need to visit the SharePoint Admin center. This is available from the Admin->Admin Centers option on Office 365.  We want to be able to visit the Site Collection Management area and create a new site collection. We can access that area directly by vising this link:

https://ozkary.sharepoint.com/_layouts/15/online/SiteCollections.aspx

Note: Replace ozkary with the corresponding site  tenant name.

On the site collection page, we can press the New button and create a Developer Site collection. In our example, we name or site collection dev which create URL similar to sites/dev. We can use any meaningful name.

https://ozkary.sharepoint.com/sites/dev

Upload the Solution

Now that we have a developer site collection, we can upload the WSP solution. This is done by visiting the developer site collection that we created on the previous section.  We can now select Site Settings-> Web Designer Galleries > Solutions or just visit this link:

https://ozkary.sharepoint.com/sites/dev/_catalogs/solutions

We are now ready to upload and activate our solution by pressing the Upload button and selecting our solution file. Once uploaded, the Activate button becomes enable. We just need to press it to activate the solution.

Enable the Feature

All SharePoint solutions have components or features that need to be enabled independently.  Once the solution is active, the features become available either at the Site or Site Collection level. This depends on how the scope of the feature is set.

When set teh scope to Web, the feature is installed at the site level. When set the scope to Site, the feature is available for the entire site collection.  For our example, we have a site scope feature, so we need to enable it from Site Settings -> Site Actions -> Manage Site features.  We could also just visit this direct link:

https://ozkary.sharepoint.com/sites/dev/_layouts/15/ManageFeatures.aspx

We should be able to find the feature name and press the Activate button.

Checking the Content

Now that we have activated the feature, all the content in our solution feature should be deployed. To validate, we need to visit Site Contents menu option. This should display the new data lists and other content that was deployed.

I hope this is able to provide an overview on how to upload a solution to Office 365.

Thanks for reading.

Originally published by ozkary.com

Parsing JSON with SQL Server

With SQL Server JSON support, we can now store JSON documents in a table. This is very similar to how we have been able to store XML documents in an Xml Column. In this article, we take a look at parsing a complex JSON document with nested structures to return a flat structure similar to a database query.

Define the JSON Document

We start by defining our JSON structure. The data may not make much sense, but the intend here is to be able to flatten this structure into a table format.  We want to be able to return every player name with the team and associated score for that team. Teams and scores are mapped by the index number on the array.  It may not be clear, but there are some challenges here. 

DECLARE @json nvarchar(1000) = N'[     {               "player":{"name":"ozkary"},         "scores":[7,9],               "teams": [                      {"name":"team one"},{"name":"team two"}                ]             },     {               "player":{"name":"dani"},         "scores":[6,10] ,               "teams": [                      {"name":"team a"},{"name":"team b"}                    ]      }  ]

For example, how can we get the name properties for both player and team’s fields? Let’s look at those fields, and we can see that one is an object. The other is an array of objects.  In addition, the scores are stored in an array of values with no correlation to anything else.

We can try to parse this data to see how it looks once it is flatten.  We start by querying an object property. This can be done by providing the document path to that property. In this case, we can get the player name with this path:

'$.player.name'

The dollar sign ($) provides the root scope of the document. From there, we walk the properties down the different levels.

When it comes to arrays, we need to do a cross apply with the array and return the data as JSON. This enables us to join with that document section and parse it. We continue to repeat that process until we get to the object of each array element. This is shown in the example below, we return the teams property as JSON, and we then cross apply on that field to select the name.

teams nvarchar(max) '$.teams' AS JSON, scores nvarchar(max) '$.scores' AS JSON CROSS APPLY OPENJSON(teams) WITH (     team nvarchar(50) '$.name' )

Now that we understand the approach, we can implement a  solution that can help us get the information. The entire query should look as follows:

DECLARE @json nvarchar(2500) = N'[     {               "player":{"name":"ozkary"},         "scores":[7,9],               "teams": [                      {"name":"team one"},{"name":"team two"}                ]              },     {               "player":{"name":"dani"},         "scores":[6,10] ,               "teams": [                      {"name":"team a"},{"name":"team b"}                    ]      }  ]'SELECT     player.name, team, score FROM OPENJSON (@json) WITH(       name nvarchar(50) '$.player.name',     teams nvarchar(max) '$.teams' AS JSON,        scores nvarchar(max) '$.scores' AS JSON ) as player CROSS APPLY OPENJSON(teams) WITH (     team nvarchar(50) '$.name' )CROSS APPLY OPENJSON(scores) WITH (     score nvarchar(50) '$' )

We first open the entire JSON document with the OPENJSON function.  We select all the fields we need using the WITH directive. For every field that we return as JSON, we do a cross apply and open that JSON segment.  This enables us to select the object properties that we need. 

The resulting data should look as show below:

As we shown here, processing JSON documents with SQL Server is feasible, but we need to be mindful that JSON structures are defined to match an application model and attempting to query the data as a table structure may be a difficult task.

Thanks for reading.

Originally published by ozkary.com