XML in, JSONB out: Best-Effort XML → JSONB in YugabyteDB YSQL

				
					CREATE OR REPLACE FUNCTION xml_to_jsonb_generic(p_xml text)
RETURNS jsonb
LANGUAGE plpgsql
AS $$
DECLARE
  v_xml        text := p_xml;
  v_children   jsonb := '{}'::jsonb;  -- key -> array of values
  v_result     jsonb := '{}'::jsonb;

  pos          int := 1;
  xml_len      int;
  sub          text;
  start_rel    int;
  start_tag_pos int;
  end_tag_rel  int;
  end_tag_pos  int;
  tag_full     text;
  tag_name_raw text;
  tag_name     text;

  after_open_pos int;
  sub_after_open text;
  close_rel      int;
  close_pos      int;
  close_end_pos  int;
  inner_text     text;
  closing        text;

  child_value   jsonb;
  existing_vals jsonb;

  k             text;
  arr           jsonb;
BEGIN
  -- Strip XML declaration like <?xml ...?>
  v_xml := regexp_replace(v_xml, '^\s*<\?xml[^>]*>\s*', '', 'g');
  xml_len := length(v_xml);

  -- Walk the XML text and parse tags one by one
  WHILE pos <= xml_len LOOP
    sub := substring(v_xml from pos);
    start_rel := strpos(sub, '<');
    IF start_rel = 0 THEN
      EXIT; -- no more tags
    END IF;

    start_tag_pos := pos + start_rel - 1;

    -- Skip closing tags like </foo>
    IF substring(v_xml from start_tag_pos+1 for 1) = '/' THEN
      pos := start_tag_pos + 2;
      CONTINUE;
    END IF;

    -- Skip declarations / comments / DOCTYPE: <?...>, <! ... >
    IF substring(v_xml from start_tag_pos+1 for 1) IN ('?', '!') THEN
      sub := substring(v_xml from start_tag_pos);
      end_tag_rel := strpos(sub, '>');
      IF end_tag_rel = 0 THEN
        EXIT;
      END IF;
      pos := start_tag_pos + end_tag_rel;
      CONTINUE;
    END IF;

    -- Find end of opening tag: <tag ...>
    sub := substring(v_xml from start_tag_pos);
    end_tag_rel := strpos(sub, '>');
    IF end_tag_rel = 0 THEN
      EXIT; -- malformed
    END IF;
    end_tag_pos := start_tag_pos + end_tag_rel - 1;

    -- Extract tag name (first token, trim any trailing '/')
    tag_full := substring(v_xml from start_tag_pos+1 for end_tag_pos - start_tag_pos - 1);
    tag_name_raw := split_part(tag_full, ' ', 1);
    tag_name := trim(trailing '/' from tag_name_raw);

    -- Self-closing tag like <foo/> → treat as empty value
    IF right(tag_full, 1) = '/' THEN
      inner_text := '';
      close_end_pos := end_tag_pos + 1;
    ELSE
      -- Find matching closing tag </tag_name>
      closing := '</' || tag_name || '>';
      after_open_pos := end_tag_pos + 1;

      sub_after_open := substring(v_xml from after_open_pos);
      close_rel := strpos(sub_after_open, closing);
      IF close_rel = 0 THEN
        -- Malformed; treat as empty
        inner_text := '';
        close_end_pos := end_tag_pos + 1;
      ELSE
        close_pos := after_open_pos + close_rel - 1;
        inner_text := substring(v_xml from after_open_pos for close_pos - after_open_pos);
        close_end_pos := close_pos + length(closing);
      END IF;
    END IF;

    -- Determine value: nested structure or plain text?
    IF position('<' IN inner_text) > 0 THEN
      child_value := xml_to_jsonb_generic(inner_text);
    ELSE
      child_value := to_jsonb(btrim(inner_text));
    END IF;

    -- Append child_value to v_children[tag_name] array
    existing_vals := v_children -> tag_name;
    IF existing_vals IS NULL THEN
      v_children := v_children || jsonb_build_object(tag_name, jsonb_build_array(child_value));
    ELSE
      v_children :=
        jsonb_set(
          v_children,
          ARRAY[tag_name],
          existing_vals || jsonb_build_array(child_value),
          true
        );
    END IF;

    -- Advance position
    pos := close_end_pos;
  END LOOP;

  -- If no children parsed, return leaf text
  IF v_children = '{}'::jsonb THEN
    RETURN to_jsonb(btrim(v_xml));
  END IF;

  -- Convert arrays into either scalar or array in final result
  FOR k, arr IN SELECT key, value FROM jsonb_each(v_children) LOOP
    IF jsonb_array_length(arr) = 1 THEN
      v_result := v_result || jsonb_build_object(k, arr->0);
    ELSE
      v_result := v_result || jsonb_build_object(k, arr);
    END IF;
  END LOOP;

  RETURN v_result;
END;
$$;