How to make API Request in SQL Server | Pt. 2

Now that we made our request, we need to parse the response. In this part, we will get exposed to the OPENJSON functionality provided by SQL Server (Compatibility 130 or Greater) that allows us to take JSON objects and parse them for values. Quickly creating a way for us to make a standard table with our JSON data.

Video Resources:
--------------------------------------------------
Resource: GitHub File

Resources:
--------------------------------------------------

Support Sigma Coding:
--------------------------------------------------

Related Topics:
--------------------------------------------------
Title: How to make API Request in SQL Server | Pt. 2

Title: How to make API Request in SQL Server | Pt. 1

Title: Working with Excel Files in SQL Server

Title: Using Pivot in TSQL | Pt. 1

Title: Using Pivot in TSQL | Pt. 2

**Amazon Associates Disclosure:
--------------------------------------------------

Tags:
--------------------------------------------------
#SQL #API #JSON

[houstonfirefox]

Very nice presentation. Informative without over-explaining. I'm a DBA of 25+ years and recently ran into a developer calling CURL from xp_cmdshell to perform API operations knowing there had to be a better way. She is now simplifying her code and building out our API call libraries using your method. Thanks for putting this up sir!

[bobmcc]

Awesome! Just finished watching parts 1 & 2. Explanations were clear and concise. Thanks so much for putting this out there!

[drewj]

Thank you so much for this video and for part 1. Super helpful and easy to follow!!

[monthlysurvey]

This was fantastic. Thank you so much.

[IvanHernandez-cojy]

Muchas gracias por el tutorial, me está sirviendo mucho y qué pena que tengas tantas vistas y pocos hayan agradecido tu esfuerzo, no les costaba nada dejar un LIKE. Gracias!

[jonnguyen]

How would one POST a file in this case? I'm having issues getting it to work.

[lazyj-wbeo]

Hey, thank you for the video. This is all very new to me but I am super eager to learn about all this. I only recently got started with the whole ETL process, SQLs and APIs etc. My goal is to visualize data in Power BI and connect it to an SQL database. The connecting Power BI to a database is no problem, but the query result should be stored as table in the database.
So my question would be: How would one store this query result in a table in your database? I am using SQL server management studio. Thank you in advance :)

[ericxls]

Great video! Thank you. Let’s see if I can put this in practice....

[garimapatel]

I am using the same approach but I am getting error 'JSON text is not properly formatted. Unexpected character '"' found at some position. How to handle this?

[shubhammahajan]

when i'm formatting json with 'with' keyword its putting null values in table

[jesusdiaz]

Awesome video. It really helped. I've implemented your process and it works. The only issue I'm having is when scheduling a job to run my SP. It's dying out due to what seems like a malformed JSON response from the API itself. Odd thing is I call the stored procedure manually and it works just fine. It's only when the job calls the executing of my stored procedure that it throws an error. Question: any way to edit the 'responseText' to remove/replace the "double-quotes" which seem to be what's causing my scheduled job to error out? Or any way to limit the "fields/properties" that are returned in the JSON 'responseText'?

[brechtmels]

what could be my mistake if i only get half a json string back? It returns a string of 65536 characters but in postman I get a string of 400k characters.

[franlago]

What do i need to do if i have a body in the request?

[jphumani]

Hi there Im getting a null response. Is there any email i cant write you? I cant send you url here cause the site is blocking it.

[marcusholm]

Great video!
Currently I'm having trouble with joining the parsed values to a table in my own database. Any tips?

[AndrewCoppock]

Getting an unexpected 0x80072EE2 on the Send doing a GET which I have working in POSTMAN.