Skip to content

Instantly share code, notes, and snippets.

@bertwagner
bertwagner / XmlReaderTest()
Last active November 24, 2016 13:48
Method for parsing XML using XmlReader
public static void XmlReaderTest(string filePath)
{
// We create storage for ids of all of the rows from users where reputation == 1
List<string> singleRepRowIds = new List<string>();
using (XmlReader reader = XmlReader.Create(filePath))
{
while (reader.Read())
{
if (reader.IsStartElement())
@bertwagner
bertwagner / XmlDocumentTest()
Last active November 24, 2016 13:48
Parsing XML data using XmlDocument
public static void XmlDocumentTest(string filePath)
{
List<string> singleRepRowIds = new List<string>();
XmlDocument doc = new XmlDocument();
doc.Load(filePath);
singleRepRowIds = doc.GetElementsByTagName("row").Cast<XmlNode>().Where(x => x.Attributes["Reputation"].InnerText == "1").Select(x => x.Attributes["Id"].InnerText).ToList();
}
@bertwagner
bertwagner / RunPerformanceTest()
Last active November 24, 2016 13:49
Method for running other XML methods through a performance test
public static double RunPerformanceTest(string filePath, Action<string> performanceTestMethod)
{
Stopwatch sw = new Stopwatch();
int iterations = 50;
double elapsedMilliseconds = 0;
// Run the method 50 times to rule out any bias.
for (var i = 0; i < iterations; i++)
{
@bertwagner
bertwagner / CarInventory.json
Last active January 16, 2017 00:27
Car Inventory JSON
{
"Cars": [{
"Make": "Volkswagen",
"Model": {
"Base": "Golf",
"Trim": "GL"
},
"Year": 2003,
"PurchaseDate": "2006-10-05T00:00:00.000Z"
}, {
@bertwagner
bertwagner / FOR JSON.sql
Created January 25, 2017 00:12
SQL Server 2016 JSON's FOR JSON
-- Create our table with test data
DROP TABLE IF EXISTS ##Garage;
CREATE TABLE ##Garage
(
Id int IDENTITY(1,1),
Make varchar(100),
BaseModel varchar(50),
Trim varchar(50),
Year int,
PurchaseDate datetime2
@bertwagner
bertwagner / JSON_MODIFY 3.sql
Last active February 6, 2017 22:02
SQL Server 2016 JSON's MODIFY_JSON Delete Property
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z", "SellDate" : "2017-02-17T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }'
-- Let's remove the PurchaseDate property on my original Volkswagen Golf since it's not relevant anymore:
SET @garage = JSON_MODIFY(@garage, '$.Cars[0].PurchaseDate', NULL)
SELECT @garage
-- Output: { "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "SellDate" : "2017-02-17T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017,
@bertwagner
bertwagner / JSON_MODIFY 2.sql
Last active February 6, 2017 22:04
SQL Server 2016 JSON's MODIFY_JSON
-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'
-- I decided to sell my Golf. Let's add a new "SellDate" property to the JSON saying when I sold my Volkswagen.
-- If we use strict mode, you'll see we can't add SellDate because the key never existed before
--SELECT JSON_MODIFY(@garage, 'append strict $.Cars[0].SellDate', '2017-02-17T00:00:00.000Z')
-- Output: Property cannot be found on the specified JSON path.
-- However, in lax mode (default), we have no problem adding the SellDate
SELECT JSON_MODIFY(@garage, 'append lax $.Cars[0].SellDate', '2017-02-17T00:00:00.000Z')
@bertwagner
bertwagner / JSON_MODIFY 1.sql
Last active February 6, 2017 22:04
SQL Server 2016 JSON's MODIFY_JSON
-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'
-- I upgraded some features in my Volkswagen recently, technically making it equivalent to a "GLI" instead of a "GL".
-- Let's update our JSON using JSON_MODIFY:
SET @garage = JSON_MODIFY(@garage, '$.Cars[0].Model.Trim', 'GLI')
SELECT @garage
-- Output: { "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }
@bertwagner
bertwagner / SQL Basic Json.sql
Last active February 6, 2017 22:05
SQL Server 2016 JSON's direct JSON string creation
-- The most primative way of creating JSON in SQL. We don't want to have to do this
DECLARE @garage nvarchar(100) = '{ "Cars" : [{ "Make" : "Volkswagen"}, { "Make" : "Subaru"}] }'
-- But it works!
SELECT @garage
-- Output: { "Cars" : [{ "Make" : "Volkswagen"}, { "Make" : "Subaru"}] }
-- And with our SQL 2016 ISJSON() function we can check that the JSON string is valid
SELECT ISJSON(@garage)
-- Output: 1
@bertwagner
bertwagner / FOR JSON PATH.sql
Last active February 6, 2017 22:08
SQL Server 2016 JSON's FOR JSON PATH
-- PATH will format a result using dot syntax in the column aliases. Here's an example with just default column names
SELECT Make, BaseModel, Trim, Year, PurchaseDate
FROM ##Garage
FOR JSON PATH, ROOT('Cars');
-- Output: {"Cars":[{"Make":"Volkswagen","BaseModel":"Golf","Trim":"GL","Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","BaseModel":"Impreza","Trim":"Premium","Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}
-- And here is the same example, just assigning aliases to define JSON nested structure
SELECT Make, BaseModel as [Model.Base], Trim AS [Model.Trim], Year, PurchaseDate
FROM ##Garage
FOR JSON PATH, ROOT('Cars');