Skip to main content

Read any Json data into a table structure using Dynamic SQL and save time write long queries.

Let's say you have a MYSQL database that stores form data as a JSON string. To read the data and manipulate it as SQL, you can use SQL link services. Here's how it works:

First, you create a temporary table to store the data from MYSQL. You can use the following SQL script to do this:

sql
/****** Object:  Table [dbo].[tbljson]    Script Date: 29/03/2023 10:37:48 AM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[#tbljson](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[form_id] [int] NOT NULL,
	[params] [nvarchar](max) NULL,

 CONSTRAINT [PK_tbljson] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Once you've created the temporary table, you can insert the values from the MYSQL database into it using the following SQL script:


insert into #tbljson
	select * from openquery(CONAN,'SELECT form_id,params FROM homejm4.tbl_convertforms_conversions where form_id=2;')

Note that the openquery statement uses link services to connect to MYSQL.

Next, you can construct a table that can be manipulated as SQL by dynamically generating the table fields based on the JSON structure. This has the advantage of automatically updating the table fields if the JSON structure changes.

To dynamically generate the table fields, you can use the following SQL script:


DECLARE @TableName NVARCHAR(100) = 'tbljson'
DECLARE @Columns NVARCHAR(MAX) = ''

SELECT @Columns = @Columns + 'JSON_VALUE(Params, ''$."' + [key] + '"'' ) AS ' + QUOTENAME([key]) + ',' 
FROM (
    SELECT DISTINCT [key] FROM #tbljson
    CROSS APPLY OPENJSON(Params)
) AS Keys

DECLARE @Query NVARCHAR(MAX) = 'SELECT ' + LEFT(@Columns, LEN(@Columns) - 1) + ' FROM ' + @TableName

PRINT @Query -- optionally print the query to check before executing

EXEC(@Query)

The script uses OPENJSON to parse the JSON string and extract the keys. It then constructs a dynamic SQL query that selects the values of each key as separate columns. Finally, the script prints the SQL query (optional) and executes it to construct the table that can be manipulated as SQL.

Comments

Post a Comment

Popular posts from this blog

Removing HTML Tags from Text Using SQL Server User-Defined Function

  Introduction: In this blog post, we'll explore how to create and use a SQL Server User-Defined Function (UDF) to remove HTML tags from a text string. This function can be handy when you need to extract plain text from HTML content stored in your database. Creating the Function: First, let's create the SQL Server UDF named udf_StripHTML . This function takes a VARCHAR(MAX) parameter @HTMLText , which represents the HTML content from which we want to remove the tags. It returns a VARCHAR(MAX) value, representing the text stripped of HTML tags. sql SET QUOTED_IDENTIFIER ON GO  CREATE FUNCTION [dbo].[udf_StripHTML] ( @HTMLText VARCHAR (MAX)) RETURNS VARCHAR (MAX) AS BEGIN DECLARE @Start INT   DECLARE @End INT   DECLARE @Length INT   SET @Start = CHARINDEX( '<' , @HTMLText )  SET @End = CHARINDEX( '>' , @HTMLText , CHARINDEX( '<' , @HTMLText ))  SET @Length = ( @End - @Start ) + 1   WHILE @Start > 0 AND @...
Post a Comment
Read more

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF.   The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008   ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.        Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.        Give this account browse access on the reporting server for the desired reports. 3.        To get this working in visual studio 2010 (I am using t...
1 comment
Read more

Creating a Link Server to MYSQL from MSSQL

There might be situations where you need to apply CRUD  operations ( Create,   Read,   Update,   Delete ) on data residing in a MYSQL server database from SQL server. In order to do this you will need to create a link service from SQL server to MYSQL. The Steps are outlined below: Steps: 1. Install the MYSQL ODBC driver on the server with the MSSQL server installation. 2. Enter details for the MYSQL ODBC settings. 3. Create a link service to MYSQL using SSMS ( SQL Server Management Studio) 4. You will need an user account on the mysql server which can perform CRUD operations  along with enable remote connections to the MYSQL installation. (See Troubleshooting steps below) 4. Test the connection. Install MYSQL ODBC Driver  Download   (http://dev.mysql.com/downloads/connector/odbc/) and install the latest MYSQL ODBC driver from the MYSQL server site. Select the version which corresponds to your installation of MSSQL usually the 64bit version. Note This...
Post a Comment
Read more