This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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()) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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++) | |
{ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"Cars": [{ | |
"Make": "Volkswagen", | |
"Model": { | |
"Base": "Golf", | |
"Trim": "GL" | |
}, | |
"Year": 2003, | |
"PurchaseDate": "2006-10-05T00:00:00.000Z" | |
}, { |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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" }] } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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'); |
OlderNewer