Lua

CREATE OR REPLACE TABLE XML_USERS(xml VARCHAR(2000));
INSERT INTO XML_USERS VALUES(
'<?xml version="1.0" encoding="UTF-8"?>
<users>
<user active="1">
<first_name>Manuel</first_name>
<last_name>Neuer</last_name>
</user>
<user active="1">
<first_name>Joe</first_name>
<last_name>Hart</last_name>
</user>
<user active="0">
<first_name>Oliver</first_name>
<last_name>Kahn</last_name>
</user>
</users>');

CREATE OR REPLACE LUA SCALAR SCRIPT process_users(xml VARCHAR(2000))
EMITS (firstname VARCHAR(20), lastname VARCHAR(20)) AS

require("lxp")
local in_user_tag = false;
local in_first_name_tag = false;
local in_last_name_tag = false;
local current = {}
local users = {}

p = lxp.new(
    {StartElement = function(p, tag, attr)
        if tag == "user" and attr.active == "1" then in_user_tag = true; end
        if tag == "first_name" then in_first_name_tag = true; end
        if tag == "last_name" then in_last_name_tag = true; end
    end,
    EndElement = function(p, tag)
        if tag == "user" then in_user_tag = false; end
        if tag == "first_name" then in_first_name_tag = false; end
        if tag == "last_name" then in_last_name_tag = false; end
    end,
    CharacterData = function(p, txt)
        if in_user_tag then
        if in_first_name_tag then current.first_name = txt; end
        if in_last_name_tag then current.last_name = txt; end
    end
    if current.first_name and current.last_name then
        users[#users+1] = current
        current = {}
    end
    end})

function run(ctx)
    p:parse(ctx.xml); p:parse(); p:close();
    for i=1, #users do
        ctx.emit(users[i].first_name, users[i].last_name)
    end
end
/

SELECT process_users(xml) from xml_users;

CREATE OR REPLACE LUA SCALAR SCRIPT server_response(url VARCHAR(500))
EMITS (response VARCHAR(10000)) AS

socket = require("socket")
ssl = require("ssl")
                      

-- HTTP simple response parse
--  Status line ( HTTP/1.0 200 OK )
--  Headers (multiple headers are allowed, Content-Length handling, etc) 
--  Body  
function parse_http_response(response)
    local status_line, headers, body = "", {}, ""
    local header_data = {}
    local body_start_index = 1

    -- Extract status line
    local header_end_index = response:find("\r\n")
    status_line = response:sub(1, header_end_index - 1)
    body_start_index = header_end_index + 2

    -- Extract headers
    while true do
        local line_end = response:find("\r\n", body_start_index)
        if not line_end or line_end == body_start_index then
            body_start_index = line_end and (line_end + 2) or body_start_index
            break
        end

        local line = response:sub(body_start_index, line_end - 1)
        body_start_index = line_end + 2

        local key, value = line:match("^(.-):%s*(.*)")
        if key and value then
            local lower_key = key:lower()
            headers[lower_key] = value
            header_data[#header_data + 1] = line
        end
    end

    -- Read body
    if headers["transfer-encoding"] == "chunked" then
        local chunks = {}
        while true do
            local chunk_size_end = response:find("\r\n", body_start_index)
            if not chunk_size_end then break end

            local chunk_size_str = response:sub(body_start_index, chunk_size_end - 1)
            local chunk_size = tonumber(chunk_size_str, 16)
            body_start_index = chunk_size_end + 2

            if chunk_size == 0 then break end

            local chunk = response:sub(body_start_index, body_start_index + chunk_size - 1)
            table.insert(chunks, chunk)
            body_start_index = body_start_index + chunk_size + 2  -- Skip over chunk and CRLF
        end
        body = table.concat(chunks)
    elseif headers["content-length"] then
        local content_length = tonumber(headers["content-length"])
        body = response:sub(body_start_index, body_start_index + content_length - 1)
    else
        body = response:sub(body_start_index)
    end

    return {
        status_line = status_line,
        headers = headers,
        header_data = table.concat(header_data, "\n"),
        body = body
    }
end

-- Function to handle HTTPS requests (using socket.tcp and ssl.wrap)
function simple_https_request(request_params)
    local parsed_url = require("socket.url").parse(request_params.url)

    local peer = socket.tcp()
    peer:connect(parsed_url.host, parsed_url.port or 443)

    -- TLS/SSL client parameters
    local params = {
      mode = "client",
      protocol = "tlsv1_2",
      verify = "none", -- No verification
      options = "all",
    }

    -- TLS/SSL initialization
    local peer = assert( ssl.wrap(peer, params) )
    assert(peer:dohandshake())

    local request = string.format("%s %s HTTP/1.1\r\nHost: %s\r\n", 
        request_params.method or "GET",
        parsed_url.path or "/",
        parsed_url.host)

    -- Add headers
    for k,v in pairs(request_params.headers or {})do
        request = request .. string.format("%s: %s\r\n", k, v)
    end

    request = request .. "Connection: close\r\n\r\n"

    peer:send(request)

    -- Read response
    local raw_response = {}
    while true do
        local chunk, status, partial = peer:receive(1024)
        if chunk then
            table.insert(raw_response,chunk)
        elseif partial and #partial>0 then
            table.insert(raw_response,partial)
            break
        end
        if status == "closed" then break end
    end

    peer:close()

    return parse_http_response(table.concat(raw_response)) 
end

function run(ctx)

    local request_params={
        url = ctx.url,
        method = "GET",
        headers = {["User-Agent"]="Simple LuaSocket HTTPS Client"},
    }

    response = simple_https_request(request_params)
    ctx.emit(response.status_line)
    ctx.emit(response.header_data)
    ctx.emit(string.sub(response.body,1,100))

end
/

SELECT server_response ('https://www.example.com') FROM DUAL;