In my previous article, I explained how a university assignment led me to run Python inside PostgreSQL to validate XML documents against an XSD.
It worked. I completed the assignment, learned something new about PostgreSQL, and moved on.
Two years later, I revisited the project with a different question in mind: how much of that solution actually needed Python?
PostgreSQL already provides functions for generating XML, querying its contents, and extracting values into relational columns. What it doesn't provide natively is validation against an arbitrary XML Schema Definition (XSD).
But while reviewing the original files, I found something I hadn't fully appreciated at the time.
Some XML documents produced by the SQL query didn't satisfy the very XSD they were supposed to follow.
That turned a simple comparison of database features into a more interesting engineering question. Before deciding where XML validation should happen, we need to know whether the document and its schema agree on what valid data looks like.
Let's explore what PostgreSQL can do on its own, what PL/Python added to the original solution, and how I would approach the same decision today.
One assignment, two different problems
Looking back at the assignment, I realized I'd been treating "XML processing" as a single requirement.
It was actually two separate workflows.
The first started with relational data. I needed to query employee records, including their job history, and generate an XML document from the results.
The second worked in the opposite direction. I needed to receive an XML document, validate it against an XSD, extract its values, and insert them into relational tables.
Here's the difference:
GENERATING XML
Relational tables
|
v
SQL
|
v
XML document
RECEIVING XML
XML document
|
v
XSD validation
|
v
SQL extraction
|
v
Relational tables
Both workflows involve XML, but that doesn't mean they require the same tools.
Generating a document is about transforming relational data into an XML structure. Receiving a document may introduce an additional requirement: proving that the document follows a defined schema.
That distinction matters because parsing XML and validating an XML contract are different operations.
PostgreSQL handles much of the first workflow, and part of the second, without needing Python at all.
The unexpected problem in my original assignment
Before getting into the PostgreSQL features, there's a discovery worth discussing.
One of my original SQL queries generated an XML document containing employee details and job history records.
An employee could have no previous jobs, one previous job, or several. The relational data model naturally allowed those possibilities.
The XML Schema, however, didn't account for all of them.
It defined an optional jobHistories container, but once that container appeared, the schema expected exactly one jobHistory element.
Why exactly one?
Because the nested jobHistory declaration didn't specify minOccurs or maxOccurs. In XSD, both default to 1.
That became a problem when I recreated the query using three synthetic employees.
One had no job history, another had one record, and the third had two.
I generated the XML and checked each document against the unchanged historical XSD.
Here's what happened:
| Job history records | Generated XML | XSD result |
|---|---|---|
| 0 | An empty <jobHistory/> element |
Invalid |
| 1 | One populated <jobHistory>
|
Valid |
| 2 | Two <jobHistory> elements |
Invalid |
The first failure happened because the query generated an empty jobHistory element. The schema required that element to contain fields such as startDate.
The third failed for a different reason. The XML contained two job history records, but the XSD allowed only one.
And this is the important part: none of those failures meant PostgreSQL had generated malformed XML.
The documents were well-formed. They simply didn't satisfy the contract defined by the schema.
That raises a question the validator cannot answer for us.
Should the schema allow multiple job history records? Or was the query supposed to return only one?
If an employee has no job history, should the document omit the optional container instead of generating an empty record?
Those are questions about requirements and data modeling, not about which XML parser to use.
A validator can tell us when two components disagree. It can't decide which one represents the correct business requirement.
For me, that was the most useful discovery in the retrospective. Before worrying about whether to use PostgreSQL, Python, or something else for validation, I needed to make sure the XML and XSD described the same contract.
The three cases are reproduced in 04-historical-output-xsd-cases.sql.
What PostgreSQL can already do with XML
Now let's look at the operations that don't require Python.
PostgreSQL supports a collection of SQL/XML features for working with XML data directly in SQL.
Rather than listing every function, I'll focus on the ones that mattered for the assignment.
Generating XML from relational data
Suppose we want to generate an XML document containing an employee's name and salary.
We can do that with SQL alone:
SELECT XMLELEMENT(
NAME employee,
XMLFOREST(
'Ada'::text AS "firstName",
1200.50::numeric AS salary
)
) AS document;
The resulting XML is equivalent to:
<employee>
<firstName>Ada</firstName>
<salary>1200.50</salary>
</employee>
Two functions do the work here.
XMLELEMENT creates an XML element, while XMLFOREST turns SQL expressions into child elements.
For more complex documents, PostgreSQL also provides XMLATTRIBUTES and XMLAGG.
XMLATTRIBUTES lets us add attributes to an element. XMLAGG combines XML values from multiple rows, which is particularly useful when dealing with relationships such as an employee's job history.
In the reconstructed experiment, I used these features to generate documents from employees with zero, one, and multiple job history records.
The example also checks ordering, omitted null fields, and the expected output structure.
You can find it in 01-native-xml-generation.sql.
One detail is worth clarifying: the XML generation itself uses native PostgreSQL functions and doesn't require PL/Python. The reproducible test script also validates the generated document against an example XSD, and that additional validation step does use lxml through PL/Python.
In other words, PostgreSQL builds the document. Python checks whether it follows the chosen schema.
Extracting XML into relational columns
The reverse operation is also supported natively.
Let's say an application sends us this document:
<employee>
<firstName>Ada</firstName>
<salary>1200.50</salary>
</employee>
We can turn those values into SQL columns using XMLTABLE:
SELECT employee.first_name, employee.salary
FROM XMLTABLE(
'/employee'
PASSING XMLPARSE(
DOCUMENT
'<employee>
<firstName>Ada</firstName>
<salary>1200.50</salary>
</employee>'
)
COLUMNS
first_name text PATH 'firstName',
salary numeric PATH 'salary'
) AS employee;
The result looks like any other SQL query:
| first_name | salary |
|---|---|
| Ada | 1200.50 |
XMLPARSE interprets the XML document. XMLTABLE then uses XPath expressions to locate the elements and convert their values into the SQL types we've specified.
We haven't written any Python code.
For extracting known values into relational columns, that's often all we need.
There's more than XMLTABLE
PostgreSQL also provides xpath(), xpath_exists(), and XMLEXISTS for querying XML documents.
Namespaces require some care, though.
In one of my tests, a document used a default XML namespace. A simple XPath expression such as /employee returned no rows because it didn't match the namespace-qualified element.
Declaring an explicit namespace alias through XMLNAMESPACES and then using /e:employee solved the problem.
There's also a limitation worth remembering: PostgreSQL 18's XML querying features rely on XPath 1.0. They aren't a complete XQuery implementation.
Still, these functions cover the generation and extraction operations I needed to demonstrate.
So where does that leave PL/Python?
What PL/Python actually contributed
The first article covered the implementation in detail, so I won't repeat the full validator here.
The key idea was to use PostgreSQL's plpython3u extension to execute Python and its lxml library inside the database server.
That allowed me to validate an incoming XML document against an XSD before extracting and inserting its values.
Looking at the solution today, I can draw a much clearer boundary around what Python was doing.
I didn't need it to build XML elements, execute XPath queries, convert a salary into a numeric column, or insert a row.
I needed it because the assignment required XSD validation inside PostgreSQL.
Those aren't interchangeable capabilities.
PostgreSQL can recognize well-formed XML and perform conversions to SQL types. But that doesn't mean it has verified every rule in an XML schema.
An XSD can define required elements, element ordering, occurrence limits, and accepted data types. PostgreSQL 18 doesn't provide built-in validation of arbitrary documents against those schemas.
PL/Python gave me a way to fill that gap.
It also introduced a different set of responsibilities.
The plpython3u language is untrusted. Python code runs within the PostgreSQL server's process, and creating functions in that language requires superuser privileges.
Deploying this approach means dealing with Python dependencies, function ownership, access permissions, parser behavior, and database-server maintenance.
The reconstructed experiment includes parser restrictions and tests for malformed or potentially problematic inputs. Those checks help make the experiment more controlled, but they don't turn it into a production-ready security design.
For my university assignment, the trade-off was understandable. Keeping the validation inside PostgreSQL was part of the constraint I was trying to satisfy.
In a real application, though, I'd first ask whether the database server was the right place for that responsibility.
What if validation happened in the application?
Here's another approach.
Suppose XML documents arrive through an API or an integration service. That component already has to receive the input, identify the request, and decide how to handle failures.
Instead of sending the XML to PostgreSQL for schema validation, the application could validate it first using an XSD-capable library.
The flow would look like this:
Incoming XML
|
v
API / Ingestion service
|
v
XSD validation
|
Valid?
/ \
No Yes
| |
Reject Extract data
|
v
PostgreSQL
This keeps the XML validation dependencies outside the database server and places input validation closer to the point where the document enters the system.
It also lets PostgreSQL focus on storing and protecting the resulting data.
At first glance, that separation is attractive.
But there's a catch.
Application-side validation only enforces the contract if every relevant write path goes through it.
Imagine that an API checks every incoming XML document, but a batch-processing job can write directly to the same tables.
That job might bypass the validation entirely.
The system therefore needs a clear answer to questions such as who can write to the database, which operations are permitted, and what invariants PostgreSQL must enforce independently.
Moving validation out of the database doesn't remove the need for a trustworthy persistence boundary.
It changes where the responsibility lives.
I haven't implemented or benchmarked an equivalent application-side validator as part of this experiment. So I can't honestly say that it would be faster, more secure, or easier to operate for this particular workload.
Those outcomes would depend on the application's architecture, deployment environment, and security requirements.
Still, it's an alternative I would definitely evaluate before choosing an in-database Python implementation.
So, when would I use PL/Python?
After reviewing the project, I'd start with the actual requirement rather than the available extensions.
Here's how I'd approach the different XML-related tasks:
| Requirement | Where I'd start |
|---|---|
| Generate XML from relational rows | PostgreSQL's native SQL/XML functions |
| Extract known fields from XML |
XMLTABLE or XPath |
| Validate an XSD inside PostgreSQL | Evaluate PL/Python with an XSD-capable library |
| Validate external XML before persistence | An ingestion service or application, with controlled database writes |
These aren't universal rules. They're starting points for evaluating a design.
If the task is simply to generate an XML document from existing tables, I'd use the native SQL/XML features first.
If I need to extract a handful of known values from XML, XMLTABLE is a natural option to investigate.
If schema validation must happen inside PostgreSQL, PL/Python becomes a possible solution, but one that needs deliberate consideration of its operational and security implications.
And if I control the application receiving the XML, I'd examine whether that is the more appropriate validation boundary.
Before making any of those choices, however, I'd verify that the schema actually represents the expected document.
The job history mismatch taught me why that step shouldn't be taken for granted.
Choosing a validator won't fix an incorrect contract.
Looking back, what would I do differently?
When I originally developed the assignment, my goal was fairly narrow: validate XML against an XSD while staying with PostgreSQL.
I found a way to do that, and it was a worthwhile technical exercise. It introduced me to PL/Python and encouraged me to explore capabilities I hadn't previously used.
But revisiting the project changed what I consider the most interesting part of the problem.
Today, I wouldn't begin by asking how to run Python in PostgreSQL.
I'd ask what the system needs to guarantee, which component owns that guarantee, and whether the XML schema correctly describes the data we expect to exchange.
Only then would I choose the tools.
The historical mismatch between the generated documents and their XSD makes that lesson particularly clear. The SQL could produce well-formed XML, and the validator could correctly reject it, while the underlying disagreement about the data contract remained unresolved.
That's the difference between making a technical solution work and understanding whether the system is doing the right thing.
PL/Python solved a specific limitation in my original assignment. I wouldn't rule it out today, but I wouldn't reach for it automatically either.
The real engineering decision isn't whether PostgreSQL can execute Python. It's whether executing Python inside PostgreSQL is the right way to meet the system's requirements.
And sometimes, the most useful thing about revisiting an old solution isn't finding a better implementation. It's learning to ask a better question.
Source code and references
The reproducible examples are available in smaje99/postgresql-xml-plpython, published under the GPL-3.0-only license.
The recorded experiments use PostgreSQL 18.6.
- Native SQL/XML generation
- XML queries, extraction, and namespaces
- Historical XML/XSD compatibility tests
- PostgreSQL 18 XML functions
- PostgreSQL SQL/XML limitations
- PostgreSQL XML data type
Version note: As of October 15, 2026, PostgreSQL 19 RC 1 was available. Its development documentation still did not describe built-in XSD validation. I haven't tested these examples on PostgreSQL 19; the experimental results discussed here come from PostgreSQL 18.6. PostgreSQL 19 remains a prerelease, so this observation is provisional.
This article presents a retrospective analysis of an educational experiment, not a universal production architecture recommendation.
Top comments (0)