Justin Cooney

Asking Copilot to Generate SQL with Data from a Specific Web Site as a Source

2024-01-01
In a previous post I looked at getting MS Copilot to generate a tSQL code snippet to get a list of countries and their GDP and to insert this into a SQL Server temporary table variable. Here is the post:

Using MS Copilot to Generate SQL Server Code to List Countries and their GDP – Justin Cooney (jwcooney.com)

The tSQL generated by Copilot is accurate, but the Country and GDP data is coming from some unknown/unspecified location. But what if we know where we /want to get the data from, can we tell Copilot where to get its data In this post I’ll take a look at updating our prompt.

Setting up the Prompt

My test today is to see if I can point Copilot to a specific feed from where to get its data. In this case I’ll try pointing Copilot at the WorldOMeter site where it lists Countries and their GDPs. There is lots else on the site which I’m sure that Copilot can also mine, but the recent list of companies and their GDP is listed here: GDP by Country – Worldometer (worldometers.info)

To set up our prompt to Copilot we’ll need to combine two queries:
1. The query to get Copilot to summarize the information on the WorldOMeter site
2. The prompt to ask Copilot to set up a tSQL table variable and then populate the table with Country and GDP information
Get Copilot to Summarize the Information on the WorldOMeter Site

We can prompt Copilot to summarize the country GDP information from the WorldOMeter site using:
```
Get a list of countries and their GDP from https://www.worldometers.info/gdp/gdp-by-country/
```
The prompt to ask Copilot to set up a tSQL table variable and then populate the table with Country and GDP information

As I explained in my previous article, we can get Copilot to create a table variable and populate it using this prompt:
```
write a sql server temporary table variable and add in the top ten countries and their gdp. Insert the countries directly and do not assume that a table exists to select from
```
Combined Solution

We can now combine the two prompts into one prompt that will get Copilot to generate the SQL and populate it with data from the WorldOMeter site:
```
write a sql server temporary table variable and add in a list of countries and their GDP from https://www.worldometers.info/gdp/gdp-by-country/ Insert the countries directly and do not assume that a table exists to select from
```
This prompt seems to work well, and Copilot generates things with data from the WorldOMeter site:
```
DECLARE @GDP TABLE (

    Country VARCHAR(100),

    GDP MONEY

);



INSERT INTO @GDP (Country, GDP)

VALUES

    ('United States', 25462700000000),

    ('China', 17963200000000),

    ('Japan', 4231140000000),

    ('Germany', 4072190000000),

    ('India', 3385090000000),

    ('United Kingdom', 3070670000000),

    ('France', 2782910000000),

    ('Russia', 2240420000000),

    ('Canada', 2139840000000),

    ('Italy', 2010430000000);



SELECT * FROM @GDP;
```
This is quite neat as it means that you can ask Copilot to both generate code as well as to analyze data published on information Web sites and to do it in one go.
Using MS Copilot to Generate SQL Server Code to List Countries and their GDP

2023-12-31
MS Copilot (formerly known as Bing Chat with GPT-4) can be useful not just for generating funny images and random trivia, but for actual, useful code snippets. I’ve been playing around with this to see what it can do and what kind of prompts it takes to get the code that I want.

For this example I’m interested in getting Copilot to create a tSQL code snippet that will create a temporary table variable and then insert a list of ten countries into it along with their latest annual GDP.

Although the question seems fairly straightforward to me, getting the code results I wanted out of Copilot took some trial and error including it getting stuck in a loop where it wouldn’t remove code that I told it I didn’t want.

From my experience here, it sems that Copilot encourages iteratively refining answers based on an initial prompt. However it does not seem very good at revising its initial premise and I eventually ended up having to revise my initial prompt in a new session.

What Didn’t Work

My initial prompt was:
```
write a sql server temporary table variable and add in the top ten countries and their gdp
```
In response Copilot generated the following code:
```
DECLARE @temp_table TABLE (

    country VARCHAR(50),

    gdp FLOAT

);
INSERT INTO @temp_table (country, gdp)

SELECT TOP 10 country, gdp

FROM original_table

ORDER BY gdp DESC;
```
So overall, the code is ok, but not really what I was asking for. Copilot correctly generated the temporary table variable with the correct columns, but off the bat Copilot seems to assume that I want to select from a pre-existing table in a pretend database. What I in fact actually want is for Copilot to get a list of countries/GDP by itself and then enter this directly in the SQL. How to properly tell Copilot to do this?

I tried the prompt below to get Copilot to do an insert of countries directly. Copilot quickly answered back ‘Sure’ and then spat back the identical SQL as it initially had provided.
```
add into the temporary table ten countries and their gdp
```
I ended up trying to brute-force the issue by asking Copilot to directly add a country and its GDP, but this was not successful and Copilot went into a loop where I could not get it to remove selecting from some imaginary countries table (original_table).

In the end I had to restart my query and try explaining what I wanted in more detail from the original prompt. Finally this seemed to work and I got the SQL I was expecting to see.

What Worked

Here is the prompt that ended up generating the code I was expecting to see:
```
write a sql server temporary table variable and add in the top ten countries and their gdp. Insert the countries directly and do not assume that a table exists to select from
```
From this prompt I got the following SQL:
```
DECLARE @TopTenCountries TABLE (

    CountryName VARCHAR(50),

    GDP FLOAT

);



INSERT INTO @TopTenCountries (CountryName, GDP)

VALUES

    ('United States', 22.67),

    ('China', 16.16),

    ('Japan', 5.15),

    ('Germany', 4.17),

    ('India', 3.05),

    ('United Kingdom', 2.62),

    ('France', 2.58),

    ('Brazil', 2.05),

    ('Italy', 1.93),

    ('Canada', 1.65);



SELECT * FROM @TopTenCountries;
```
Take Away Thoughts

As you can see, Copilot does seem to generate code snippets quickly and accurately. Here are my take-away thoughts based on the simple code example above:
1. Copilot encourages refining the original results with further prompts from the user, assuming its original results are not incorrect.
2. If the initial results to your prompt are not correct then it may be easier to start the session from scratch rather than convincing Copilot to fix its results.
3. In your initial prompt to Copilot, it is best to be extra descriptive and know what copilot may do wrong in advance and write your prompt to discourage the incorrect results. You can only know this by seeing the wrong results first so it may be useful to make a mental note of past attempts with Copilot.
All in all, the snippets that Copilot writes are useful, especially if you know what you want. If you already know a language, then it may be faster to write your code yourself, but if you are not familiar with the language, then the code that Copilot writes may be useful to you.

SQL Server Search Through XML Rows Stored in a Table Column

2023-11-15

There might come a day where you need to query a SQL Server table that has embedded XML rows. This is basically like having a table inside of a table. A simple example of where you might find this is in historical logging. So for example this might be a historical table where each day data is saved into a table row with a time stamp, and a bunch of historical information is saved as XML into the table.

A Pet Store Example

Let’s imagine that we have a pet store that has daily backups of its pets into a SQL Server historical table called PetsHist. That table has a timestamp as well as an XML datatype column that saves the pets at the store on that day.

Id	Year	Month	Day	Pets
1	2023	11	16	`<root>` `<row pet="cat" type="tabby" name="mittens" />` `<row pet="dog" type="poodle" name="spot" />` `<row pet="cat" type="calico" name="stripes"/>` `</root>`
2	2023	11	17	`<root>` `<row pet="cat" type="tabby" name="mittens" />` `<row pet="dog" type="poodle" name="spot" />` `<row pet="cat" type="calico" name="stripes"/>` `<row pet="cat" type="tabby" name="paws"/>` `</root>`

Expected Output

In the example table above you can see we have two historical rows containing XML data in stored in the row attributes. For audit purposes we’d like a report of what types of cats were in the store on each day.

So basically, this is the output we want to see:

Year	Month	Day	Pet	Type	Name
2023	11	16	Cat	tabby	`mittens`
2023	11	16	Cat	calico	`stripes`
2023	11	17	Cat	tabby	`mittens`
2023	11	17	Cat	calico	`stripes`
2023	11	17	Cat	tabby	`paws`

T-SQL Code

To output the table above from the SQL Server table and the embedded XML potentially containing many rows in the XML we can use a temporary table variable, a cursor, and a row-level XML query. Here is the code:

DECLARE @TableName TABLE ( 
Year INT,
Month INT,
Day INT,
Pet varchar(200),
Type varchar(200),
Name varchar(200)
)
DECLARE @XMLFile XML
declare @Year INT
declare @Month INT
declare @Day INT
DECLARE db_cursor CURSOR FOR 
SELECT  
      [Year]
      ,[Month]
      ,[Day]
      ,[Pets] 
FROM  PetsHist
where Year=2023
order by Day asc

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @Year,@Month,@Day,@XMLFile  

WHILE @@FETCH_STATUS = 0  
BEGIN  
INSERT INTO @TableName
SELECT @Year,@Month,@Day,
   ref.value('@Pet[1]', 'VARCHAR (100)') AS Pet
  ,ref.value('@Type[1]', 'VARCHAR (100)') AS Type
  ,ref.value('@Name[1]', 'VARCHAR (100)') AS Name
FROM @XMLFile.nodes('/root/row') xmlData( ref )
WHERE  ref.value('@Type[1]', 'VARCHAR (100)') = 'cat'

      FETCH NEXT FROM db_cursor INTO @Year,@Month,@Day,@XMLFile  
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

select * from @TableName

Summary

The code above will give us the results we need.

For starters we create a temporary table that we will populate as we get what we want from the existing table and split out the XML rows
Within the cursor we can query the XML field of that row just like a table now. We can look for one or more rows in the XML containing the data that we want. To loop through each row in the XML we use the syntax that defines what each row is from the root XML node: FROM @XMLFile.nodes('/root/row') xmlData( ref )
To get a specific attribute like ‘Pet‘ in the row we use the syntax: ref.value('@Pet[1]', 'VARCHAR (100)') AS Pet

Justin Cooney

recent posts

about

Asking Copilot to Generate SQL with Data from a Specific Web Site as a Source

Setting up the Prompt

Get Copilot to Summarize the Information on the WorldOMeter Site

The prompt to ask Copilot to set up a tSQL table variable and then populate the table with Country and GDP information

Combined Solution

Using MS Copilot to Generate SQL Server Code to List Countries and their GDP

What Didn’t Work

What Worked

Take Away Thoughts