Sometimes you want to store and parse HTML or more generally XML data in a database table. Note:
Source: http://www.postgresql.org/docs/9.2/inte ... e-xml.htmlThe 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
In addition, PostgreSQL has a lot of inbuilt functions to operate on XML data.
2. XML functions.
http://www.postgresql.org/docs/9.2/inte ... s-xml.htmlThe 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.
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";');
Code: Select all
<?php echo "hello world";?>
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;
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;
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%';
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