Re: [PoC] Add CANONICAL option to xmlserialize - Mailing list pgsql-hackers

The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:       tested, failed
Spec compliant:           tested, failed
Documentation:            tested, failed

Hi Jim,
I tested the function with dynamically generated XML using `XMLELEMENT`, `XMLATTRIBUTES`, and `XMLCOMMENT`.

Example:

SELECT XMLCANONICALIZE(XMLELEMENT(
    NAME employee,
    XMLCOMMENT('Employee details start'),
    XMLELEMENT(NAME id, 101),
    XMLELEMENT(NAME name, 'Mahesh'),
    XMLELEMENT(NAME department, 'Engineering'),
    XMLCOMMENT('Employee details end')
), true);

The function correctly handled comments and structured XML elements as expected.

Testing with a Table Containing Employee Data
Created a table and inserted sample employee records to verify XML generation.

Table Creation and Data Insertion:

CREATE TABLE employees (id INT, name TEXT, department TEXT);
INSERT INTO employees VALUES (1, 'Alice', 'HR'), (2, 'Bob', 'IT');


Canonicalizing Employee XML Data:

SELECT XMLCANONICALIZE(XMLELEMENT(
    NAME employee,
    XMLATTRIBUTES(id AS id),
    XMLCOMMENT('Employee details start'),
    XMLELEMENT(NAME name, name),
    XMLELEMENT(NAME department, department),
    XMLCOMMENT('Employee details end')
), true)
FROM employees;

The patch correctly processes XML elements and attributes from database records.

Testing with Dynamic Comments
Added a column to store dynamic comments and verified if `XMLCANONICALIZE` handles them properly.

Modifications:
ALTER TABLE employees ADD COLUMN comment TEXT;
UPDATE employees SET comment = 'Employee details for ' || name;


Verification Query:

SELECT XMLCANONICALIZE(XMLELEMENT(
    NAME employee,
    XMLATTRIBUTES(id AS id),
    XMLCOMMENT(comment),
    XMLELEMENT(NAME name, name),
    XMLELEMENT(NAME department, department)
), true)
FROM employees;

Dynamic comments were correctly included in the XML output.

Testing with Manual Newlines
Inserted manual newlines to check the function's behavior with formatted XML.


SELECT XMLCANONICALIZE(
    XMLELEMENT(
        NAME employee,
        XMLATTRIBUTES(id AS id),
        XMLCOMMENT(comment),
        E'\n  ',
        XMLELEMENT(NAME name, name),
        E'\n  ',
        XMLELEMENT(NAME department, department),
        E'\n'
    ),
    true
)
FROM employees;

Whitespace and newlines were correctly handled in XML output.

After testing various scenarios, I found that `XMLCANONICALIZE` is working as expected. It:
- Removes unnecessary whitespace and newlines.
- Sorts attributes in a consistent order.
- Converts empty elements to start-end tag pairs.
- Preserves or removes comments based on the flag.
- Works well with table data and dynamically generated XML.

Regards,
NewtGlobal PostgreSQL contributors