In today’s fast-paced development world, efficiency is paramount. AI can significantly accelerate the coding process, including the creation of scripts to interact with various data formats. This blog post will explore how to use AI assistance to generate Python code for loading a custom XML file.<\/p>\n\n\n\n
This is a use case for those of us who work with data, that we can use to solve challenges which we have in development. While it’s a specific example, the approach is very re-usable and can be applied to many sources and databases.<\/p>\n\n\n\n
For the sake of simplicity, and what’s going to be familiar to most data engineers (including open source software), I’m using an XML file I found, and coding in python loading to a MySQL database.<\/p>\n\n\n\n
For anyone who isn’t so familiar with it (most technologies use json now) XML <\/strong>(Extensible Markup Language) is a simple, text-based format for representing structured data. It’s like a universal language that computers and humans can both understand. <\/p>\n\n\n\n Here’s a breakdown:<\/strong><\/p>\n\n\n\n A simple sample example is:<\/p>\n\n\n\n For this example, I’ve found an XML file which contains nested structured data. We can envisage having a collection of these files which we have to understand and load into a format which is usable for data consumption (cut down example below, full file can be seen on my github<\/a>).<\/p>\n\n\n\n Historically I’d use a local text editor (with syntax highlighting) to explore the file and work out what objects are in it, or an online equivalent such as: https:\/\/jsonformatter.org\/xml-formatter<\/a><\/p>\n\n\n\n In this case, I’m using ChatGPT<\/a> to speed up my process. You can see the whole conversation here<\/a> (the relevant parts are copied below).<\/p>\n\n\n\n Yes, XML is very human readable, with indentation it’s even clearer what the hierarchy is. But asking AI to do some of this is even more readable.<\/p>\n\n\n\n Describe the hierarchy of data structures in the attached XML file<\/p>\n<\/blockquote>\n\n\n\n The hierarchy of data structures in the XML file is organized as follows:<\/p>\n\n\n\n Root Element<\/strong>:<\/p>\n\n\n\n Volume<\/strong>:<\/p>\n\n\n\n Disc<\/strong>:<\/p>\n\n\n\n Parent<\/strong>:<\/p>\n\n\n\n Content<\/strong>:<\/p>\n\n\n\n Keywords<\/strong>:<\/p>\n\n\n\n This hierarchy reflects a clear structure to organize animated background content across collections, discs, and specific items within those discs.<\/p>\n<\/details>\n\n\n\n Convert this xml into tables for product, volume, disc, and content<\/p>\n<\/blockquote>\n\n\n\n Here\u2019s a tabular representation of the XML data, broken down by Product, Volume, Disc, and Content.<\/p>\n\n\n\n Table: Product<\/strong><\/p>\n\n\n\n This approach captures a normalized structure suitable for a database. Each table includes key fields that uniquely identify and relate the elements. If needed, additional rows can be added for the remaining content.<\/p>\n<\/details>\n\n\n\n It didn’t get it exactly right, an ID was missing, fortunately I can ask for it to be added<\/p>\n\n\n\n include disc id for content, volume id for disc, product id for volume<\/p>\n<\/blockquote>\n\n\n\n Here’s the updated tabular representation with the Product ID included in the Volume table:<\/p>\n\n\n\n Table: Product<\/strong><\/p>\n\n\n\n\n
<name><\/code>, <age><\/code>) to define the structure and meaning of the data.<\/li>\n\n\n\n<person>\n <name>John Doe<\/name>\n <age>30<\/age>\n <city>New York<\/city>\n<\/person><\/pre>\n\n\n\n
My (more complicated) XML file<\/h3>\n\n\n\n
<?xml version=\"1.0\" encoding=\"utf-8\"?>\n<product id=\"Animated Canvases\" name=\"Animated Canvases\" version=\"1\" singlecontent=\"true\">\n<volume id=\"Animated Canvases Collection 3\" name=\"Animated Canvases Collection 3\" number=\"3\" sourcefiletype=\"mov\" preview_discid=\"ACNV_V03D01D_R1\" preview_suffix=\".mov\" preview_install_dir=\"Juicer3\" preview_path_on_disc=\"Juicer3\/\" preview_thumbnail_suffix=\".jpg\" install_size_img=\"1\" install_size_img_mov=\"10\" totalPreviewSize=\"185\" totalSourceSize=\"14464.62\" baseJ3Version=\"3.03 Build 258\">\n<disc id=\"ACNV_V03D01D_R1\" number=\"1\">\n<parent id=\"Source\">\n<content id=\"All Star_HD\" type=\"1\" name=\"\" originalfps=\"30\" frames=\"300\" description=\"Animated Background\" resolution=\"hd\" resx=\"1920\" resy=\"1080\" base=\"\"><keywords><\/keywords><\/content>\n<content id=\"Amber Rays_HD\" type=\"1\" name=\"\" originalfps=\"30\" frames=\"300\" description=\"Animated Background\" resolution=\"hd\" resx=\"1920\" resy=\"1080\" base=\"\"><keywords><\/keywords><\/content>\n<content id=\"Approaching Doom_HD\" type=\"1\" name=\"\" originalfps=\"30\" frames=\"600\" description=\"Animated Background\" resolution=\"hd\" resx=\"1920\" resy=\"1080\" base=\"\"><keywords><\/keywords><\/content>\n<\/parent>\n<\/disc>\n<disc id=\"ACNV_V03D02D_R1\" number=\"2\">\n<parent id=\"Source\">\n<content id=\"Hiding Magic_HD\" type=\"1\" name=\"\" originalfps=\"30\" frames=\"300\" description=\"Animated Background\" resolution=\"hd\" resx=\"1920\" resy=\"1080\" base=\"\"><keywords><\/keywords><\/content>\n<content id=\"Higher Than The Sky_HD\" type=\"1\" name=\"\" originalfps=\"30\" frames=\"450\" description=\"Animated Background\" resolution=\"hd\" resx=\"1920\" resy=\"1080\" base=\"\"><keywords><\/keywords><\/content>\n<\/parent>\n<\/disc>\n<\/volume>\n<\/product><\/pre>\n\n\n\n
Using AI<\/h2>\n\n\n\n
Viewing the structure and content of the file<\/h3>\n\n\n\n
\n
Click to view full response<\/summary>\n
<product><\/code>: Represents the top-level element of the file. It includes attributes like id<\/code>, name<\/code>, version<\/code>, and others. This serves as the main container for all subsequent elements.<\/p>\n\n\n\n<volume><\/code>: Nested within <product><\/code>. Represents a collection of discs and related content. It includes attributes such as id<\/code>, name<\/code>, number<\/code>, and metadata about file types and sizes.<\/p>\n\n\n\n<disc><\/code>: Nested within <volume><\/code>. Represents individual discs in the collection. Each <disc><\/code> has attributes like id<\/code> and number<\/code>.<\/p>\n\n\n\n<parent><\/code>: Nested within <disc><\/code>. Represents a grouping for source content, identified by an id<\/code> attribute, usually set to “Source”.<\/p>\n\n\n\n<content><\/code>: Nested within <parent><\/code>. Represents individual animated background items. Each <content><\/code> element contains attributes like id<\/code>, type<\/code>, name<\/code>, description<\/code>, resolution<\/code>, and frame-related metadata (e.g., originalfps<\/code>, frames<\/code>).<\/p>\n\n\n\n<keywords><\/code>: Nested within <content><\/code>. A placeholder element for additional metadata about each animated background item. It is currently empty in the provided XML file.<\/p>\n\n\n\nExample Hierarchy<\/h3>\n\n\n\n
\n
<product><\/code>\n\n
<volume><\/code>\n\n
<disc><\/code>\n\n
<parent><\/code>\n\n
<content><\/code>\n\n
<keywords><\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n\n
Click to view full response<\/summary>\n
ID<\/strong><\/th> Name<\/strong><\/th> Version<\/strong><\/th> SingleContent<\/strong><\/th><\/tr><\/thead> Animated Canvases<\/td> Animated Canvases<\/td> 1<\/td> true<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\nTable: Volume<\/strong><\/h3>\n\n\n\n
ID<\/strong><\/th> Name<\/strong><\/th> Number<\/strong><\/th> SourceFileType<\/strong><\/th> PreviewDiscID<\/strong><\/th> PreviewSuffix<\/strong><\/th> PreviewInstallDir<\/strong><\/th> PreviewPathOnDisc<\/strong><\/th> PreviewThumbnailSuffix<\/strong><\/th> InstallSizeImg<\/strong><\/th> InstallSizeImgMov<\/strong><\/th> TotalPreviewSize<\/strong><\/th> TotalSourceSize<\/strong><\/th> BaseJ3Version<\/strong><\/th><\/tr><\/thead> Animated Canvases Collection 3<\/td> Animated Canvases Collection 3<\/td> 3<\/td> mov<\/td> ACNV_V03D01D_R1<\/td> .mov<\/td> Juicer3<\/td> Juicer3\/<\/td> .jpg<\/td> 1<\/td> 10<\/td> 185<\/td> 14464.62<\/td> 3.03 Build 258<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\nTable: Disc<\/strong><\/h3>\n\n\n\n
ID<\/strong><\/th> VolumeID<\/strong><\/th> Number<\/strong><\/th><\/tr><\/thead> ACNV_V03D01D_R1<\/td> Animated Canvases Collection 3<\/td> 1<\/td><\/tr> ACNV_V03D02D_R1<\/td> Animated Canvases Collection 3<\/td> 2<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\nTable: Content<\/strong><\/h3>\n\n\n\n
ID<\/strong><\/th> DiscID<\/strong><\/th> Type<\/strong><\/th> Name<\/strong><\/th> OriginalFPS<\/strong><\/th> Frames<\/strong><\/th> Description<\/strong><\/th> Resolution<\/strong><\/th> ResX<\/strong><\/th> ResY<\/strong><\/th> Base<\/strong><\/th> Keywords<\/strong><\/th><\/tr><\/thead> All Star_HD<\/td> ACNV_V03D01D_R1<\/td> 1<\/td> <\/td> 30<\/td> 300<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr> Amber Rays_HD<\/td> ACNV_V03D01D_R1<\/td> 1<\/td> <\/td> 30<\/td> 300<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr> Approaching Doom_HD<\/td> ACNV_V03D01D_R1<\/td> 1<\/td> <\/td> 30<\/td> 600<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr> Balloon Rise_HD<\/td> ACNV_V03D01D_R1<\/td> 1<\/td> <\/td> 30<\/td> 600<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr> Blending Pipes_HD<\/td> ACNV_V03D01D_R1<\/td> 1<\/td> <\/td> 30<\/td> 450<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr> Bundle Of Joy_HD<\/td> ACNV_V03D01D_R1<\/td> 1<\/td> <\/td> 30<\/td> 600<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr> Center Of Attention_HD<\/td> ACNV_V03D01D_R1<\/td> 1<\/td> <\/td> 30<\/td> 300<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr> Hiding Magic_HD<\/td> ACNV_V03D02D_R1<\/td> 1<\/td> <\/td> 30<\/td> 300<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr> Higher Than The Sky_HD<\/td> ACNV_V03D02D_R1<\/td> 1<\/td> <\/td> 30<\/td> 450<\/td> Animated Background<\/td> hd<\/td> 1920<\/td> 1080<\/td> <\/td> <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n\n
Click to view full response<\/summary>\n
ID<\/strong><\/th> Name<\/strong><\/th> Version<\/strong><\/th> SingleContent<\/strong><\/th><\/tr><\/thead> Animated Canvases<\/td> Animated Canvases<\/td> 1<\/td> true<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\nTable: Volume<\/strong><\/h3>\n\n\n\n
ID<\/strong><\/th> ProductID<\/strong><\/th> Name<\/strong><\/th> Number<\/strong><\/th> SourceFileType<\/strong><\/th> PreviewDiscID<\/strong><\/th> PreviewSuffix<\/strong><\/th> PreviewInstallDir<\/strong><\/th> PreviewPathOnDisc<\/strong><\/th> PreviewThumbnailSuffix<\/strong><\/th> InstallSizeImg<\/strong><\/th> InstallSizeImgMov<\/strong><\/th> TotalPreviewSize<\/strong><\/th> TotalSourceSize<\/strong><\/th> BaseJ3Version<\/strong><\/th><\/tr><\/thead>