Page 1 of 1

XML and Json datatypes in PostgreSQL.

Posted: Thu May 09, 2013 12:46 pm
by KBleivik
1. Background

Sometimes you want to store and parse HTML or more generally XML data in a database table. Note:
The xml data type can be used to store XML data. Its advantage over storing XML data in a text field is that it checks the input values for well-formedness, and there are support functions to perform type-safe operations on it
Source: http://www.postgresql.org/docs/9.2/inte ... e-xml.html

In addition, PostgreSQL has a lot of inbuilt functions to operate on XML data.

2. XML functions.
The functions and function-like expressions described in this section operate on values of type xml. Check Section 8.13 for information about the xml type. The function-like expressions xmlparse and xmlserialize for converting to and from type xml are not repeated here. Use of most of these functions requires the installation to have been built with configure --with-libxml.
http://www.postgresql.org/docs/9.2/inte ... s-xml.html

Processing XML and mapping tables to XML.

Note that you can process XML with the XPath function and map tables to XML data. You should also note that there is a function xmlpi expression creates an XML processing instruction

Example:

Code: Select all

SELECT xmlpi(name php, 'echo "hello world";');
which produces the output:

Code: Select all

 <?php echo "hello world";?>
3. Converting SQL/XML Output to HTML

Scroll down to figure 9.1 XSLT Stylesheet for Converting SQL/XML Output to HTML : http://www.postgresql.org/docs/9.2/inte ... s-xml.html

4. JSON Type

The json data type can be used to store JSON (JavaScript Object Notation) data. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. For more information see: http://www.postgresql.org/docs/9.2/inte ... -json.html

In addition there are related support functions: http://www.postgresql.org/docs/9.2/inte ... -json.html You should also note that there are unsupported devel versions: http://www.postgresql.org/docs/devel/st ... -json.html

Related link: http://www.ietf.org/rfc/rfc4627.txt

5. Examples

Example 1.

Code: Select all

CREATE TABLE documents (
doc xml,
CONSTRAINT doc_is_valid CHECK(doc IS DOCUMENT)
);

Code: Select all

INSERT INTO documents VALUES (
'<html>
<head><title>Hello world</title></head>
<body><h1>First heading</h1></body>
</html>');

Code: Select all

SELECT
(xpath('//title/text()', doc))[1]::varchar AS title,
(xpath('//h1/text()', doc))[1]::varchar AS h1
FROM documents;
Example 2.

Code: Select all

CREATE FUNCTION document_title(xml) RETURNS varchar AS $$ SELECT
(xpath('//title/text()', $1))[1]::varchar
$$ LANGUAGE 'sql';

Code: Select all

SELECT document_title(doc) from documents;
Example 3.

Code: Select all

CREATE INDEX document_title_index
ON documents (document_title(doc) varchar_pattern_ops);

Code: Select all

SELECT * FROM documents
WHERE document_title(doc) LIKE 'Hello%';
Source: http://www.packtpub.com/instant-postgresql-starter/book

6. Related links

http://www.forumnorway.com/viewforum.ph ... 3c7e33545c

7. Litterature

Pro PHP XML and Web Services: http://www.apress.com/9781590596333

http://www.packtpub.com/instant-postgre ... /book/body