Programming question regarding epg123.xmltv

An evolving, supported alternative to Rovi
Forum rules
★ Download the latest EPG123 here: https://garyan2.github.io/ <> Setup guide here: https://garyan2.github.io/install.html
Post Reply
tirebiter

Posts: 40
Joined: Mon Jul 03, 2017 8:33 pm
Location:

HTPC Specs: Show details

Programming question regarding epg123.xmltv

#1

Post by tirebiter » Mon Oct 10, 2022 7:34 pm

A few years ago, I wrote a SQL Server script which imported the epg123.xml (previous name) into a database. It created two tables, one with the channels and the other with the shows. I could then query these tables with a join on channels.id with shows.channel to get the channel info.

At the time, the multiple "display-name" attributes in the <channel> xml elements seemed to be in a predictable order. But in the new version (epg123.xmltv) they aren't. Here is the code I originally wrote to populate the channels table:

SELECT @x = P
FROM OPENROWSET (BULK 'C:\projects\epg123new.xml', SINGLE_BLOB) AS tv(P)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT *
into channels
FROM OPENXML (@hdoc, '/tv/channel', 2)
WITH (
id varchar(50) '@id',
chan1 varchar(50) 'display-name[1]',
chan2 varchar(50) 'display-name[2]',
chan3 varchar(50) 'display-name[3]',
chan4 float 'display-name[4]',
chan5 varchar(50) 'display-name[5]',
icon varchar(100))

It fails whenever display-name[4] isn't numeric. I realize that changing that column to varchar fixes the error, but then I don't know which string is in which chanX column.

Is there some sort of switch or cascading if statement that I can use to test each display-name attribute and make a better guess on where to store it? How does the epg123 code handle this? chan4 is the most important value, so a test of numeric vs. non-numeric is sufficient.

Thanks for any help,
Dan

User avatar
garyan2

Posts: 7438
Joined: Fri Nov 27, 2015 7:23 pm
Location:

HTPC Specs: Show details

#2

Post by garyan2 » Tue Oct 11, 2022 1:16 am

Deja vu

viewtopic.php?f=99&t=13425&p=143772#p143776 shows how epg123 adds the display names.

It will be predictable with one exception... when the callsign and the station name are the same (think something like HSN2 or G4). I'm not going to repeat a display name. If what you want is only the channel numbers, then you can bring it in as char and evaluate each for being numbers only (and decimal point for ATSC/QAM).
- Gary
Keeping WMC alive beyond January 2020. https://garyan2.github.io

tirebiter

Posts: 40
Joined: Mon Jul 03, 2017 8:33 pm
Location:

HTPC Specs: Show details

#3

Post by tirebiter » Thu Oct 20, 2022 7:51 pm

Hi Gary,

My apologies for the repeated query. I couldn't get it working the first time, so I just reverted to the older epg123 version. That wasn't an option this time.

So, I finally read what you wrote and actually understood it this time. I couldn't figure out how to do it during the importing of the XML into the database, so I did the obvious thing and modified the table in the same SQL script after the data load, using database statements (duh).

I reversed what you did with the duplicate callsign and station names. If the fourth display-name attribute is null or not numeric (chan4 in the database), I shift the previous ones one column to the right. FWIW, there are 55 incidences for my epg. This relies on an assumption that the new value in chan4 is numeric. So far, that has been the case.

There was another unrelated issue. Previously there were two newlines (CRLF) embedded in the description attribute to format the Season/Episode values. That messes up my result, so I originally ran a utility that removed them before the database import. Now it seems there is only one newline in that attribute. It's harder to detect because every line in the file ends with a newline. Try though I may, using <sed> I couldn't get a Regular Expression to remove that description attribute newline, even though it worked in the TextPad editer. I finally gave up and deleted all the newlines from the file because XML ignores them anyway.

So now order is restored to the universe. Thanks again for the marvelous thing that is epg123.

Regards,
Dan

Post Reply