Three steps to use jQuery UI in ASP.NET MVC 5

[Origin]: https://blog.falafel.com/three-steps-use-jquery-ui-asp-net-mvc-5/

Many developers struggle to work with jQuery UI in an ASP.NET MVC application. In this post, I will show you three steps required to start working with jQuery UI in an ASP.NET MVC application. At the end of the post we will take a look at working with the autocomplete widget.
The following three steps will let you work with jQuery UI in an ASP.NET MVC application:
Step 1: Add the jQuery UI Reference
Add the jQuery UI reference into the project using the NuGet manager. Once this is done, you should find the reference added in the Content folder and the Scripts folder.

jqueryui1

Step 2: Bundle the required files
Open the BundleConfig.cs file. In this file add two entries, one for the jQuery UI scripts and other for jQuery UI CSS.
Add the script entry as follows:

Next add the CSS files for jQueryUI widgets.  CSS for all the widgets can be bundled like this:

For the purpose of this example, let’s say you are only working with the autocomplete widget. In this case, you would only bundle the core.css and autocomplete.css as shown below:

Step 3: Refer to the Bundles
Once the bundles for jQuery UI have been created, you need to add them to the layout file. That can be done as follows:

By default you will find the jQuery bundle at the end of the layout file. To work with jQuery UI widgets, you should move the jQuery bundle to the top of the file and also include the bundles for jQuery UI CSS and Scripts.
You have now completed the three steps necessary to work with jQueryUI in an ASP.NET MVC application.
Use jQueryUI Widgets 
Now let’s look at the autocomplete widget in action.  I have created a controller for returning JSON data as follows:

We will now bind the returned JSON from the GetMP() action to the autocomplete widget. On the razor view, you can create an autocomplete widget like this:

Make sure the view is using the layout file in which you added the reference of the bundles.
Summary 
We’ve seen in this post that working with jQuery UI widgets is as simple as following three simple steps:

  1. Add the  jQuery UI reference
  2. Bundle the required files
  3. Refer to the bundles

The autocomplete widget is also a helpful tool when working with jQuery UI bundles.
I hope this post was useful. Happy coding.

How to add jQuery UI to your ASP.NET MVC App in 3 steps

[Origin]: https://omarnegronm.wordpress.com/2015/08/29/how-to-add-jquery-ui-to-your-asp-net-mvc-project-in-3-steps/

This is a quick demo on how to add jQuery UI to your MVC project in Visual Studio 2015 but the process applies to earlier versions as well. For this example, I’ll use the accordion widget.

This is how the page looks before applying the accordion widget…

img-3

and after…

img-11The index.cshtml markup:

@{
    ViewBag.Title = "Home Page";
}
 
<div id="accordion" class="prettyprint">
    <h3>Section 1</h3>
    <div>
        <p>
            Mauris mauris ante, blandit et, ultrices a, suscipit eget, quam. Integer
            ut neque. Vivamus nisi metus, molestie vel, gravida in, condimentum sit
            amet, nunc. Nam a nibh. Donec suscipit eros. Nam mi. Proin viverra leo ut
            odio. Curabitur malesuada. Vestibulum a velit eu ante scelerisque vulputate.
        </p>
    </div>
    <h3>Section 2</h3>
    <div>
        <p>
            Sed non urna. Donec et ante. Phasellus eu ligula. Vestibulum sit amet
            purus. Vivamus hendrerit, dolor at aliquet laoreet, mauris turpis porttitor
            velit, faucibus interdum tellus libero ac justo. Vivamus non quam. In
            suscipit faucibus urna.
        </p>
    </div>
    <h3>Section 3</h3>
    <div>
        <p>
            Nam enim risus, molestie et, porta ac, aliquam ac, risus. Quisque lobortis.
            Phasellus pellentesque purus in massa. Aenean in pede. Phasellus ac libero
            ac tellus pellentesque semper. Sed ac felis. Sed commodo, magna quis
            lacinia ornare, quam ante aliquam nisi, eu iaculis leo purus venenatis dui.
        </p>
        <ul>
            <li>List item one</li>
            <li>List item two</li>
            <li>List item three</li>
        </ul>
    </div>
    <h3>Section 4</h3>
    <div>
        <p>
            Cras dictum. Pellentesque habitant morbi tristique senectus et netus
            et malesuada fames ac turpis egestas. Vestibulum ante ipsum primis in
            faucibus orci luctus et ultrices posuere cubilia Curae; Aenean lacinia
            mauris vel est.
        </p>
        <p>
            Suspendisse eu nisl. Nullam ut libero. Integer dignissim consequat lectus.
            Class aptent taciti sociosqu ad litora torquent per conubia nostra, per
            inceptos himenaeos.
        </p>
    </div>
</div>
 
@section scripts
{
    <script>
        $('#accordion').accordion();
    </script>
     
}

Notice at the bottom the @scripts section. This is defined in your _Layout.cshtml view. There you will find the @RenderSection method being called as you can see below:

_Layout.cshtml

    ...
 
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
 
    @RenderSection("scripts", required: false)
</body>
</html>

1) Install jQuery.UI.Combined

jQuery is already included in your project but not jQuery-UI. Install jQuery.UI.Combined with the Nuget Package Manager. This package includes all of the jQuery-UI widgets and animations.

img-4

A quicker way of installing it is by typing the following command in the Package Manager Console:

PM> Install-Package jQuery.UI.Combined

2) Add the jQuery-UI script and style bundles

Add the script and style bundles to your BundleConfig.cs file in the App_Start folder.

using System.Web;
using System.Web.Optimization;
 
namespace jQueryDemo
{
    public class BundleConfig
    {
        public static void RegisterBundles(BundleCollection bundles)
        {
            bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                        "~/Scripts/jquery-{version}.js"));
 
            bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
                        "~/Scripts/jquery.validate*"));
 
            // Add the jquery-ui script bundle
            bundles.Add(new ScriptBundle("~/bundles/jquery-ui").Include(
                        "~/Scripts/jquery-ui-{version}.js"));
 
            // Add the jquery-ui css bundle
            bundles.Add(new StyleBundle("~/Content/themes/base/css").Include(
                        "~/Content/themes/base/all.css"));
             
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                        "~/Scripts/modernizr-*"));
 
            bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                      "~/Scripts/bootstrap.js",
                      "~/Scripts/respond.js"));
 
            bundles.Add(new StyleBundle("~/Content/css").Include(
                      "~/Content/bootstrap.css",
                      "~/Content/site.css"));
        }
    }
}

3) Call the Script and Style Render Methods in the Layout View

Finally, call the @Scripts.Render() and @Styles.Render() methods in your _Layout.cshtml after the jQuery render method is called.

    ...
     
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
 
    <!-- Including the jquery-ui script and style bundles -->
    @Scripts.Render("~/bundles/jquery-ui")
    @Styles.Render("~/Content/themes/base/css")
 
    @RenderSection("scripts", required: false)
</body>
</html>

That’s it! Build your project, refresh the page, and enjoy the results. If you don’t include the installation, it’s basically a two-step process. Include the script and style bundles in your BundleConfig file and call the render methods from your layout view.

Applying a theme

If the base theme is too plain-looking you might want to go for one of the many different themes offered by jQuery-UI. Let’s go ahead an apply the Redmond theme to our project. Begin by installing jQuery.UI.Themes through the Nuget Package Manager. This package includes all of the jQuery themes in one download.

img-12

Once it’s installed check that the themes appear in your solution explorer in the Content > Themes folder.

img-13

Now you need to add the theme’s style bundle in your BundleConfig.cs file and remove the base theme. In our case, we’ll make sure the style bundle points to redmond/jquery-ui-redmond.css.

using System.Web;
using System.Web.Optimization;
namespace jQueryDemo
{
    public class BundleConfig
    {
        public static void RegisterBundles(BundleCollection bundles)
        {
            bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                        "~/Scripts/jquery-{version}.js"));
            bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
                        "~/Scripts/jquery.validate*"));
            
            bundles.Add(new ScriptBundle("~/bundles/jquery-ui").Include(
                        "~/Scripts/jquery-ui-{version}.js"));
            // Removing the base theme.
            // bundles.Add(new StyleBundle("~/Content/themes/base/css").Include(
            //            "~/Content/themes/base/all.css"));
            // Adding the 'redmond' style bundle.
            bundles.Add(new StyleBundle("~/Content/themes/redmond/css").Include(
                        "~/Content/themes/redmond/jquery-ui.redmond.css"));
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                        "~/Scripts/modernizr-*"));
            bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                      "~/Scripts/bootstrap.js",
                      "~/Scripts/respond.js"));
            bundles.Add(new StyleBundle("~/Content/css").Include(
                      "~/Content/bootstrap.css",
                      "~/Content/site.css"));
        }
    }
}

Finally, head to your _Layout.cshtml and change @Styles.Render(“~/Content/themes/base/css”) to @Styles.Render(“~/Content/themes/redmond/css”)

  ...
 
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
 
    <!-- Including the jquery-ui script and style bundles -->
    @Scripts.Render("~/bundles/jquery-ui")
    @Styles.Render("~/Content/themes/redmond/css")
 
    @RenderSection("scripts", required: false)
</body>
</html>

Rebuild the project and the theme should now be active when you launch the application.

img-14

View JSON in Internet Explorer 7-11

[Origin]: http://developers.de/blogs/holger_vetter/archive/2013/12/13/view-json-in-internet-explorer-7-11.aspx

Hello guys,

you all know the problem, want to debug an application with IE and IE always wants to download the JSON file.

The other browsers like Chrome or Firefox are doing a better job here.

If you want that IE shows the JSON file without downloading it, here is a registry fix for it.

Create a new text document and insert this (Link: http://pastebin.com/B0BXG0N6):

Windows Registry Editor Version 5.00;
; Tell IE 7,8,9,10,11 to open JSON documents in the browser on Windows XP and later.
; 25336920-03F9-11cf-8FD0-00AA00686F13 is the CLSID for the "Browse in place" .
;
[HKEY_CLASSES_ROOT\MIME\Database\Content Type\application/json]
"CLSID"="{25336920-03F9-11cf-8FD0-00AA00686F13}"
"Encoding"=hex:08,00,00,00

[HKEY_CLASSES_ROOT\MIME\Database\Content Type\text/json]
"CLSID"="{25336920-03F9-11cf-8FD0-00AA00686F13}"
"Encoding"=hex:08,00,00,00

Now rename the document to {FileName}.reg and save.

Run it and confirm the dialog.

Now JSON files will be shown directly in IE, without downloading.

*Thanks to Coding 101 from http://www.codeproject.com/Tips/216175/View-JSON-in-Internet-Explorer for the Script

Debugging JavaScript Using Google Chrome

[Origin]: http://www.c-sharpcorner.com/UploadFile/3d39b4/debugging-javascript-using-google-chrome/

We are working with data and data are storing in JavaScript variables. We need to see what value is passed to the function and how the value is manipulated and what result the value is producing so we need to debug the JavaScript function to trace a variable value line by line. In the previous article I explained how to debug a JavaScript function using Visual Studio and Internet Explorer; that article is:

Debugging JavaScript Code Using VS and IE

So now we go through step-by-step to debug a JavaScript function using Google Chrome.

Create UI Design and Develop Functionality for Addition

We create a UI Design that takes two values and calculates the addition of these two values on a button click.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type ="text/javascript">
        function AddNumber()
        {
            var result = document.getElementById("<%=lblResult.ClientID %>");
            var firstNumber = document.getElementById("<%=txtFirstNumber.ClientID %>").value;
            var secondNumber = document.getElementById("<%=txtSecondNumber.ClientID %>").value;
            var addition = parseInt( firstNumber) + parseInt(secondNumber);
            result.innerHTML = "Addition is :" + addition;
            return false;
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    Enter First Number : <asp:TextBox ID="txtFirstNumber" runat="server"></asp:TextBox><br />
    Enter Second Number : <asp:TextBox ID="txtSecondNumber" runat="server"></asp:TextBox><br />
    <asp:Button ID="btnAdd" runat="server" Text="Addition" OnClientClick="javascript:return AddNumber();" /><br/>
    <asp:Label ID="lblResult" runat="server"></asp:Label>
    </div>
    </form>
</body>
</html>

Value Input Window

image1.png

Add Debugger in the JavaScript function so the cursor can hit here whenever the function is called.

image2.png

Setting and Debug In Google Chrome

Go to “Tools” then click on “JavaScript console”.

image3.png
image4.png

Run the application and insert values into the input fields then click on the “Addition” button. The cursor will then reach the debugger in the source tab under the JavaScript console window.

image5.png

In this function we declare several variables and on starting, these variables are #ff0000; check the Scope Variables panel in the Right Pane.

image6.png

Click on F10 and move forward line by line, then check the value of the variables using a mouse move on that variable.

image7.png

We can also check data in the scope variables window.

image8.png

We can also check for an error in the JavaScript code. For example we don’t define a variable and use that variable. I have a variable firstNumber and use the myfirstNumber variable to parse in an int type but instead firstNumber is undefined.

image9.png

We can insert a breakpoint by double-clicking on line number or using right-click of the mouse, then click on “Add breakpoint item”. When we use a breakpoint we don’t need to define the debugger in the JavaScript function and the cursor will hit the breakpoint directly.

image10.png

We have the Breakpoints window where the line with the breakpoint checkbox checked will be shown.

image11.png

separate files (HTML5,CSS & JS)

Origin: https://stackoverflow.com/questions/12151708/separate-files-html5-css-js

Just declare references to your CSS & JS from within your HTML. For performance reasons, it’s good to put the CSS reference in the head, and the JS reference at the end of the file just before closing out the tag. That way, your page will load and will look correct right off the bat and won’t be blocked downloading the JavaScript. The JS will be the last thing to load. Here’s an example of the HTML markup:

<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyles.css" />
</head>

<body>
...

<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="myscript.js"></script>

</body>
</html>
share

JSON Data (SQL Server)

[Origin]: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

JSON is a popular textual data format used for exchanging data in modern web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases like Microsoft Azure DocumentDB. Many REST web services return results formatted as JSON text or accept data formatted as JSON. For example, most Azure services such as Azure Search, Azure Storage, and Azure DocumentDb have REST endpoints that return or consume JSON. JSON is also the main format for exchanging data between web pages and web servers using AJAX calls.

Here’s an example of JSON text:

[{
    "name": "John",
    "skills": ["SQL", "C#", "Azure"]
}, {
    "name": "Jane",
    "surname": "Doe"
}]

SQL Server provides built-in functions and operators that let you do the following things with JSON text.

  • Parse JSON text and read or modify values.
  • Transform arrays of JSON objects into table format.
  • Run any Transact-SQL query on the converted JSON objects.
  • Format the results of Transact-SQL queries in JSON format.

    Overview of built-in JSON support

Key JSON capabilities of SQL Server

Here’s more info about the key capabilities that SQL Server provides with its built-in JSON support.

Extract values from JSON text and use them in queries

If you have JSON text that’s stored in database tables, you can use built-in functions to read or modify values in the JSON text.

  • Use the JSON_VALUE function to extract a scalar value from a JSON string.
  • Use JSON_QUERY to extract an object or an array from a JSON string.
  • Use the ISJSON function to test whether a string contains valid JSON.
  • Use the JSON_MODIFY function to change a value in a JSON string.

Example

In the following example, the query uses both relational and JSON data (stored in the jsonCol column) from a table:

SELECT Name,Surname,
 JSON_VALUE(jsonCol,'$.info.address.PostCode') AS PostCode,
 JSON_VALUE(jsonCol,'$.info.address."Address Line 1"')+' '
  +JSON_VALUE(jsonCol,'$.info.address."Address Line 2"') AS Address,
 JSON_QUERY(jsonCol,'$.info.skills') AS Skills
FROM PeopleCollection
WHERE ISJSON(jsonCol)&gt;0
 AND JSON_VALUE(jsonCol,'$.info.address.Town')='Belgrade'
 AND Status='Active'
ORDER BY JSON_VALUE(jsonCol,'$.info.address.PostCode')

Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). JSON functions use JavaScript-like syntax for referencing values inside JSON text.

For more info, see Validate, Query, and Change JSON Data with Built-in Functions (SQL Server), JSON_VALUE (Transact-SQL), and JSON_QUERY (Transact-SQL).

Change JSON values

If you have to modify parts of JSON text, you can use the JSON_MODIFY function to update the value of a property in a JSON string and return the updated JSON string. The following example updates the value of a property in a variable that contains JSON.

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=JSON_MODIFY(@jsonInfo,'$.info.address[0].town','London') 

Convert JSON collections to a rowset

You don’t need a custom query language to query JSON in SQL Server. To query JSON data, you can use standard T-SQL. If you have to create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more info, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

The following example calls OPENJSON and transforms the array of objects stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob')  

Results

id firstName lastName age dateOfBirth
2 John Smith 25
5 Jane Smith 2005-11-04T12:00:00

OPENJSON transforms the array of JSON objects into a table in which each object is represented as one row, and key/value pairs are returned as cells. The output observes the following rules.

  • OPENJSON converts JSON values to the types specified in the WITH clause.
  • OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • You don’t have to return all the fields contained in the JSON text.
  • OPENJSON returns NULL values if JSON values don’t exist.
  • You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

For more info, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

Convert SQL Server data to JSON or export JSON

Format SQL Server data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. For more info, see Format Query Results as JSON with FOR JSON (SQL Server).

The following example uses PATH mode with the FOR JSON clause.

SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob  
FROM People  
FOR JSON PATH  

The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

Results

[{
    "id": 2,
    "info": {
        "name": "John",
        "surname": "Smith"
    },
    "age": 25
}, {
    "id": 5,
    "info": {
        "name": "Jane",
        "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
}] 

For more info, see Format Query Results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

Combine relational and JSON data

SQL Server provides a hybrid model for storing and processing both relational and JSON data using standard Transact-SQL language. You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly-typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables using full Transact-SQL.

JSON text is typically stored in varchar or nvarchar columns and is indexed as plain text. Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features. You can store JSON in In-memory or Temporal tables, you can apply Row-Level Security predicates on JSON text, and so on.

If you have pure JSON workloads where you want to use a query language that’s customized for the processing of JSON documents, consider Microsoft Azure DocumentDB.

Here are some use cases that show how you can use the built-in JSON support in SQL Server.

Return data from a SQL Server table formatted as JSON

If you have a web service that takes data from the database layer and returns it in JSON format, or JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query. Instead of writing code or including a library to convert tabular query results and then serialize objects to JSON format, you can use FOR JSON to delegate the JSON formatting to SQL Server.

For example, you might want to generate JSON output that’s compliant with the OData specification. The web service expects a request and response in the following format.

  • Request: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName
  • Response: {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}

    This OData URL represents a request for the ProductID and ProductName columns for the product with id 1. You can use FOR JSON to format the output as expected in SQL Server.

SELECT 'http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity'
 AS '@odata.context',   
 ProductID, Name as ProductName   
FROM Production.Product  
WHERE ProductID = 1  
FOR JSON AUTO  

The output of this query is JSON text that’s fully compliant with OData spec. Formatting and escaping are handled by SQL Server. SQL Server can also format query results in any format such as OData JSON or GeoJSON – for more info, see Returning spatial data in GeoJSON format.

Analyze JSON data with SQL queries

If you have to filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. Then use standard Transact-SQL and built-in functions to prepare the reports.

SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
FROM   SalesOrderRecord AS Tab  
          CROSS APPLY  
     OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData  
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified  

Both standard table columns and values from JSON text can be used in the same query. You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve performance of query. For more info, see Index JSON data.

Import JSON data into SQL Server tables

If you have to load JSON data from an external service into SQL Server, you can use OPENJSON to import the data into SQL Server instead of parsing the data in the application layer.

DECLARE @jsonVariable NVARCHAR(MAX)

SET @jsonVariable = N'[  
        {  
          "Order": {  
            "Number":"SO43659",  
            "Date":"2011-05-31T00:00:00"  
          },  
          "AccountNumber":"AW29825",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":1  
          }  
        },  
        {  
          "Order": {  
            "Number":"SO43661",  
            "Date":"2011-06-01T00:00:00"  
          },  
          "AccountNumber":"AW73565",  
          "Item": {  
            "Price":2024.9940,  
            "Quantity":3  
          }  
       }  
  ]'

INSERT INTO SalesReport  
SELECT SalesOrderJsonData.*  
FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData;

The content of the JSON variable can be provided by an external REST service, sent as a parameter from a client-side JavaScript framework, or loaded from external files. You can easily insert, update or merge results from JSON text into a SQL Server table. For more info about this scenario, see the following blog posts.

Load JSON files into SQL Server

Information stored in files can be formatted as standard JSON or Line-Delimited JSON. SQL Server can import the contents of JSON files, parse it using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • If your JSON documents are stored in local files, on shared network drives, or in Azure File Storage locations that can be accessed by SQL Server, you can use bulk import to load your JSON data into SQL Server. For more info about this scenario, see Importing JSON files into SQL Server using OPENROWSET (BULK).
  • If your line-delimited JSON files are stored in Azure Blob Storage or the Hadoop file system, you can use Polybase to load JSON text, parse it in Transact-SQL code, and load it into tables.

Test drive built-in JSON support

Test drive built-in JSON support with the AdventureWorks sample database. To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from here. After you restore the sample database to an instance of SQL Server 2016, unzip the samples file and open the “JSON Sample Queries procedures views and indexes.sql” file from the JSON folder. Run the scripts in this file to reformat some existing data as JSON data, run sample queries and reports over the JSON data, index the JSON data, and import and export JSON.

Here’s what you can do with the scripts included in the file.

  1. Denormalize the existing schema to create columns of JSON data.
    1. Store information from SalesReasons, SalesOrderDetails, SalesPerson, Customer, and other tables that contain information related to sales order into JSON columns in the SalesOrder_json table.
    2. Store information from EmailAddresses/PersonPhone tables into the Person_json table as arrays of JSON objects.
  2. Create procedures and views that query JSON data.
  3. Index JSON data – create indexes on JSON properties and full-text indexes.
  4. Import and export JSON – create and run procedures that export the content of the Person and the SalesOrder tables as JSON results, and import and update the Person and the SalesOrder tables using JSON input.
  5. Run query examples – run some queries that call the stored procedures and views created in steps 2 and 4.
  6. Clean up scripts – don’t run this part if you want to keep the stored procedures and views created in steps 2 and 4.

Learn more about built-in JSON support

Topics in this section

Format Query Results as JSON with FOR JSON (SQL Server)
Use the FOR JSON clause to delegate the formatting of JSON output from your client applications to SQL Server.

Convert JSON Data to Rows and Columns with OPENJSON (SQL Server)
Use OPENJSON to import JSON data into SQL Server, or to convert JSON data into relational format for an app or service that can’t currently consume JSON directly, such as SQL Server Integration Services.

Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)
Use these built-in functions to validate JSON text and to extract a scalar value, an object, or an array.

JSON Path Expressions (SQL Server)
Use a path expression to specify the JSON text that you want to use.

Index JSON data
Use computed columns to create collation-aware indexes over properties in JSON documents.

Solve common issues with JSON in SQL Server
Find answers to some common questions about the built-in JSON support in SQL Server.

Microsoft blog posts

Reference topics

Importing and Processing data from XML files into SQL Server tables

[Origin]: https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

Problem

In my last article, I talked about how you can use an  FTP task in SSIS to download files from an FTP server. But what if the file you have downloaded is an XML file and you need to import this data from the XML file into a SQL Server table? How do you process/parse XML data into SQL Server tables?

Solution

There are different ways to achieve this task of importing data from an XML file into a SQL Server table, but I am going to demonstrate one of easiest ways to accomplish this task.

These are the steps I performed for importing data into SQL Server and then parsing the XML into a relational format.

  • Import XML data from an XML file into SQL Server table using the OPENROWSET function
  • Parse the XML data using the OPENXML function

Importing XML data from XML file using OPENROWSET

I have an XML file downloaded from my FTP location to a local folder and data in this XML file looks like this:

Importing XML data from XML file using OPENROWSET

Now in order to import data from the XML file to a table in SQL Server, I am using the OPENROWSET function as you can see below.

In the script below, I am first creating a table with a column of data type XML and then reading the XML data from the file using the OPENROWSET function by specifying the file location and name of the XML file as you can see below:

CREATE DATABASE OPENXMLTesting
GO


USE OPENXMLTesting
GO


CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)


INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;


SELECT * FROM XMLwithOpenXML

When I query the table in which I have imported the XML data, it looks like this. The XMLData column is an XML data type, it will output a hyperlink as shown below:

As XMLData column is of XML data type, it will give an hyperlink

Clicking on the hyperlink, in the above image, will open another tab within SSMS with the XML data displayed as shown below.

<ROOT>
  <Customers>
    <Customer CustomerID="C001" CustomerName="Arshad Ali">
      <Orders>
        <Order OrderID="10248" OrderDate="2012-07-04T00:00:00">
          <OrderDetail ProductID="10" Quantity="5" />
          <OrderDetail ProductID="11" Quantity="12" />
          <OrderDetail ProductID="42" Quantity="10" />
        </Order>
      </Orders>
      <Address> Address line 1, 2, 3</Address>
    </Customer>
    <Customer CustomerID="C002" CustomerName="Paul Henriot">
      <Orders>
        <Order OrderID="10245" OrderDate="2011-07-04T00:00:00">
          <OrderDetail ProductID="11" Quantity="12" />
          <OrderDetail ProductID="42" Quantity="10" />
        </Order>
      </Orders>
      <Address> Address line 5, 6, 7</Address>
    </Customer>
    <Customer CustomerID="C003" CustomerName="Carlos Gonzlez">
      <Orders>
        <Order OrderID="10283" OrderDate="2012-08-16T00:00:00">
          <OrderDetail ProductID="72" Quantity="3" />
        </Order>
      </Orders>
      <Address> Address line 1, 4, 5</Address>
    </Customer>
  </Customers>
</ROOT>

Process XML data using OPENXML function

Now as I said before, XML data stored in a column of data type XML can be processed either by using XML functions available in SQL Server or by using the sp_xml_preparedocument stored procedure along with the OPENXML function.

We will first call the sp_xml_preparedocument stored procedure by specifying the XML data which will then output the handle of the XML data that it has prepared and stored in internal cache.

Then we will use the handle returned by the sp_xml_preparedocument stored procedure in the OPENXML function to open the XML data and read it.

Note: the sp_xml_preparedocument stored procedure stores the XML data in SQL Server’s internal cache, it is essential to release this stored XML data from internal cache by calling the sp_xml_removedocument stored procedure. We should call the sp_xml_removedocument stored procedure as early possible, so that internal cache can be freed for other usage.

USE OPENXMLTesting
GO


DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


SELECT @XML = XMLData FROM XMLwithOpenXML


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT CustomerID, CustomerName, Address
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer')
WITH 
(
CustomerID [varchar](50) '@CustomerID',
CustomerName [varchar](100) '@CustomerName',
Address [varchar](100) 'Address'
)


EXEC sp_xml_removedocument @hDoc
GO

From the above XML data, I want to retrieve all the customer information and hence I am navigating to the Customer element and querying CustomerID and CustomerName (please note the use of “@” before the name of the attribute) attributes and Address element in the above SELECT statement using the OPENXML function.

The structure of the resultset can be determined with the “WITH” clause as shown above.

Process XML data using OPENXML function

From the above XML data, I now want to retrieve all the customer information along with OrderID and OrderDate placed by each individual customer and hence I am navigating to the Order element and then querying OrderID and OrderDate attributes.

If we want to navigate back to the parent or grand parent level and get data from there, we need to use “../” to read the parent’s data and “../../” to read the grand parent’s data and so on.

USE OPENXMLTesting
GO


DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


SELECT @XML = XMLData FROM XMLwithOpenXML


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT CustomerID, CustomerName, Address, OrderID, OrderDate
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order')
WITH 
(
CustomerID [varchar](50) '../../@CustomerID',
CustomerName [varchar](100) '../../@CustomerName',
Address [varchar](100) '../../Address',
OrderID [varchar](1000) '@OrderID',
OrderDate datetime '@OrderDate'
)


EXEC sp_xml_removedocument @hDoc
GO

The result of the above query can be seen in the image below. You can see below all the customers and all the orders placed by each customer.

querying CustomerID and CustomerName

Now let’s go one level deeper. This time from the above XML data, I want to retrieve all the customer information and their orders along with ProductID and Quantity from each order placed. And hence, as you can see below I am navigating to the OrderDetail and retrieving the ProductID and Quantity attributes’ values. At the same time I am using “../” to reach the parent level to get Order information available at the parent level whereas I am using “../../../” to reach to the great grand parent level to grab Customer information as shown below:

USE OPENXMLTesting
GO


DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


SELECT @XML = XMLData FROM XMLwithOpenXML


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT CustomerID, CustomerName, Address, OrderID, OrderDate, ProductID, Quantity
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail')
WITH 
(
CustomerID [varchar](50) '../../../@CustomerID',
CustomerName [varchar](100) '../../../@CustomerName',
Address [varchar](100) '../../../Address',
OrderID [varchar](1000) '../@OrderID',
OrderDate datetime '../@OrderDate',
ProductID [varchar](50) '@ProductID',
Quantity int '@Quantity'
)


EXEC sp_xml_removedocument @hDoc
GO

The result of the above query can be seen in the image below. You can see all the customer information and their orders along with ProductID and Quantity from each order placed.

The result of the above query
Next Steps