Data Types - PostgreSQL: Up and Running - page 102

you how to append XML data to a table, by declaring a column as xml and inserting into
it as usual.
Example 5-30. Populate XML field
CREATE TABLE families ( id serial PRIMARY KEY , profile xml );
INSERT INTO families ( profile )
VALUES (
'<family name="Gomez">
<member><relation>padre</relation><name>Alex</name></member>
<member><relation>madre</relation><name>Sonia</name></member>
<member><relation>hijo</relation><name>Brandon</name></member>
<member><relation>hija</relation><name>Azaleah</name></member>
</family>' );
Each XML value could have a different XML structure. To enforce uniformity, you can
add a check constraint, covered in “Check Constraints” on page 111 , to the XML column.
Example 5-31 ensures that all family has at least one relation element. The '/family/
member/relation' is XPath syntax, a basic way to refer to elements and other parts of
XML.
Example 5-31. Ensure that all records have at least one member relation
ALTER TABLE families ADD CONSTRAINT chk_has_relation
CHECK ( xpath_exists ( '/family/member/relation' , profile ));
If we then try to insert something like:
INSERT INTO families ( profile ) VALUES ( '<family name="HsuObe"></family>' );
we will get this error: ERROR: new row for relation "families" violates check
constraint "chk_has_relation" .
For more involved checks that require checking against DTD or XSD, you'll need to
resort to writing functions and using those in the check constraint, because PostgreSQL
doesn't have built-in functions to handle those kinds of checks.
Querying XML Data
To query XML, the xpath function is really useful. The first argument is an XPath query,
and the second is an xml object. The output is an array of XML elements that satisfy the
XPath query. Example 5-32 combines xpath with unnest to return all the family mem‐
bers. unnest unravels the array into a row set. We then cast the XML fragment to text.
Example 5-32. Query XML field
SELECT family ,
( xpath ( '/member/relation/text()' , f ))[ 1 ]:: text As relation ,
( xpath ( '/member/name/text()' , f ))[ 1 ]:: text As mem_name
FROM ( SELECT ( xpath ( '/family/@name' , profile ))[ 1 ]:: text As family ,