How to query XML data type in a PostgreSQL database
PostgreSQL supports XML data storage and query, uses XML types to ensure correct format, extracts nodes through xpath(), filters data with xpath_exists(), and combines UNNEST to achieve multi-value expansion. It can simulate XMLTABLE structured output to meet conventional XML processing needs.
PostgreSQL doesn't have a native XML data type with built-in XQuery support like SQL Server, but it does offer robust XML handling through functions and operators. You can store XML in a column of type XML , and query its content using PostgreSQL's XML function library.
Store and Validate XML Data
When you define a column as XML , PostgreSQL ensures only well-formed XML is stored.
Example table:
CREATE TABLE documents (id serial PRIMARY KEY,
content xml
);
Insert sample data:
INSERT INTO documents (content) VALUES ('Query XML Using XPath
Use xpath() to extract nodes or values using XPath expressions. It returns a set of XML values.
Extract all titles:
SELECT (xpath('//title/text()', content))[1]::text AS title FROM documents;The result is an array — use indexing and cast to text for clean output.
Check for Node Existence
To filter rows based on XML content, use xpath_exists() (equivalent to XQuery's boolean checks).
Find documents where author is "John Doe":
SELECT * FROM documentsWHERE xpath_exists('//author[text() = "John Doe"]', content);
Extract Multiple Values
You can extract multiple fields at once using multiple xpath() calls:
SELECT(xpath('//title/text()', content))[1]::text AS title,
(xpath('//author/text()', content))[1]::text AS author
FROM documents;
If a node may appear more than once, use UNNEST to return one row per match:
SELECT UNNEST(xpath('//author/text()', content))::text AS author FROM documents;Convert XML to Text or Table
To work with structured results, combine xpath() with unnest() or wrap in XMLTABLE -like patterns using unnest(xpath(...)) .
Simulate XMLTABLE behavior:
SELECTr.title,
r.author
FROM documents,
LATERAL (SELECT
UNNEST(xpath('//title/text()', content))::text AS title,
UNNEST(xpath('//author/text()', content))::text AS author
)r;
Basically it comes down to using xpath() for extraction and xpath_exists() for filtering. While not full XQuery, it's powerful enough for most XML querying needs in PostgreSQL.
The above is the detailed content of How to query XML data type in a PostgreSQL database. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undresser.AI Undress
AI-powered app for creating realistic nude photos
ArtGPT
AI image generator for creative art from text prompts.
Stock Market GPT
AI powered investment research for smarter decisions
Hot Article
Popular tool
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
20518
7
13631
4
How to install the XML Tools plugin in Notepad ? (Plugin Manager)
Mar 05, 2026 am 12:37 AM
Notepad v8.6.1 has completely removed the PluginManager. XMLTools cannot be installed because it has not been migrated to the new plug-in system and the author has stopped updating it. Manual installation is only applicable to v8.5.7 and earlier versions. It is recommended to use built-in functions or alternatives such as VSCode.
How to convert XML to YAML for DevOps? (Configuration Management)
Mar 12, 2026 am 12:11 AM
xmltodict PyYAMListhesafestcomboforDevOpsconfigfilesbecauseitpreservescomments,CDATA,namespaces,andattributesaccurately,unlikerawXML-to-YAMLtoolsorCLIutilitieslikeyqandxmllintwhichsilentlydropcriticalmetadata.
How to format and beautify XML code in Notepad ? (Pretty Print)
Mar 07, 2026 am 12:20 AM
Notepad needs to manually install and enable the XMLTools plug-in to format XML; if the tags are messed up or the content is lost after formatting, it means that the XML itself is illegal, and there are problems such as unclosed tags or illegal characters.
How to convert an XML file to a Word document? (Reporting)
Mar 09, 2026 am 01:05 AM
python-docx does not support direct reading of XML files. You need to use xml.etree.ElementTree or lxml to parse the XML extraction fields first, and then write them into the Document object segment by segment. Explicit declaration of prefixes is required to process namespaces, and manual manipulation of the underlying XML is required for table merging and styling. Chinese paths should be avoided when saving.
How to minify XML files for faster web loading? (Performance Optimization)
Mar 08, 2026 am 12:16 AM
RunningminifyonXMLwithoutunderstandingitsrulesbreaksparsingoralterssemanticsbecausewhitespacecanbemeaningful;safeminificationrequiresdata-orientedXML,controlledgeneration/consumption,andstrictparserawareness.
How to parse XML data from a URL API? (Rest Services)
Mar 13, 2026 am 12:06 AM
To parse remote XML API in Python, you need to use requests to get the response and then check the status code and Content-Type. Prioritize using r.text with xml.etree.ElementTree to parse; when encountering a namespace, you need to pass the namespace dictionary; use iterparse to stream large files and clear them manually; front-end JS requires CORS support or proxy.
How to use Attributes vs Elements in XML? (Design Best Practices)
Mar 16, 2026 am 12:26 AM
You should use attributes to store short metadata (such as id, type), and use elements to store scalable content data; because attributes do not support namespaces, duplication, nesting, and internationalization, their parsing is error-prone and maintenance is difficult.
How to open and view XML files in Windows 11? (Beginner Guide)
Mar 12, 2026 am 01:02 AM
The XML file cannot be opened by double-clicking because it is associated with Notepad by default, causing confusion in the display. You should use Notepad, VSCode or Edge instead; Edge can format and report errors, while VSCode requires the installation of extensions such as RedHatXML for normal highlighting, indentation and verification.
