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
-- Lax (default: function will return an error if invalid JSON path specified | |
SELECT JSON_VALUE('{ "Color" : "Red" }', '$.Shape') --lax is the default, so you don't need to be explicitly state it | |
-- Output: NULL | |
SELECT JSON_VALUE('{ "Color" : "Red" }', 'lax $.Shape') | |
-- Output: NULL | |
-- Strict: function will return an error if invalid JSON path specified | |
SELECT JSON_VALUE('{ "Color" : "Red" }', 'strict $.Shape') | |
-- Output: Property cannot be found on the specified JSON path. |
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
SELECT ISJSON('{ "Color" : "Blue" }') -- Returns 1, valid | |
-- Output: 1 | |
SELECT ISJSON('{ "Color" : Blue }') -- Returns 0, invalid, missing quotes | |
-- Output: 0 | |
SELECT ISJSON('{ "Number" : 1 }') -- Returns 1, valid, numbers are allowed | |
-- Output: 1 | |
SELECT ISJSON('{ "PurchaseDate" : "2015-08-18T00:00:00.000Z" }') -- Returns 1, valid, dates are just strings in ISO 8601 date format https://en.wikipedia.org/wiki/ISO_8601 |
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" }] }' | |
SELECT JSON_VALUE(@garage, '$.Cars[0].Make') -- Return the make of the first car in our array | |
-- Output: Volkswagen | |
SELECT CAST(JSON_VALUE(@garage, '$.Cars[0].PurchaseDate') as datetime2) -- Return the Purchase Date of the first car in our array and convert it into a DateTime2 datatype | |
-- Output: 2006-10-05 00:00:00.0000000 | |
SELECT JSON_VALUE(@garage, '$.Cars') -- This returns NULL because the values of Cars is an array instead of a simple object |
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" }] }' | |
SELECT * FROM OPENJSON(@garage, '$.Cars') -- Displaying the values of our "Cars" array. We additionally get the order of the JSON objects outputted in the "key" column and the JSON object datatype in the "type" column | |
/* Output: | |
key value type | |
------ ------------------------------------------------------------------------------------------------------------------------------------ ---- | |
0 { "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 20 |
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" }] }' | |
-- Here we retrieve the Make of each vehicle in our Cars array | |
SELECT JSON_VALUE(value, '$.Make') FROM OPENJSON(@garage, '$.Cars') | |
/* Output: | |
------------ | |
Volkswagen | |
Subaru | |
*/ |
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" }] }' | |
-- This returns NULL because the values of Cars is an array instead of a simple object | |
SELECT JSON_VALUE(@garage, '$.Cars') | |
-- Output: NULL | |
-- Using JSON_QUERY() however returns the JSON string representation of our array object | |
SELECT JSON_QUERY(@garage, '$.Cars') | |
-- Output: [{ "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" }] |
OlderNewer