-- xml data type

select '<a/>'::xml;
select cast('<a/>' as xml);
-- select '<a/><b></c>'::xml; -- error: not well balanced
select xmlelement(name person);
select xmlelement(name person, 'Chris');

-- read nobel table

drop table if exists nobel cascade;
\i nobel.sql

-- a simple query

select winner, subject, yr from nobel where winner like '%Curie%';
select xmlelement(name winner, winner), subject, yr from nobel where winner like '%Curie%';

-- store query results into another table as XML data

drop table if exists xnobel;
select xmlelement(name row, xmlconcat(xmlelement(name winner, winner), xmlelement(name yr, yr ))) into xnobel from nobel where winner like '%Curie%';

-- make a view from this!

drop view if exists vxnobel;
create view vxnobel as select xmlelement(name row, xmlconcat(xmlelement(name winner, winner), xmlelement(name yr, yr ))) as record from nobel where winner like '%Curie%';
select * from vxnobel;

-- or easily transform a whole table into XML or XML Schema

select table_to_xml('nobel', false, false, '');
select table_to_xmlschema('nobel', false, false, '');

-- XPath is available too - this picks the two <a/> nodes and returns an array of type xml[]

select xpath('//a', '<b><a/><a/></b>');

-- apply the XPath location to each record in vxnobel

select record from vxnobel;
select record, xpath('//yr', record) from vxnobel;

-- let's make a bigger view and use XPath in teh where clause

drop view if exists vxallnobel;
create view vxallnobel as select xmlelement(name row, xmlconcat(xmlelement(name winner, winner), xmlelement(name yr, yr ))) as record from nobel;
select * from vxallnobel;
select * from vxallnobel where array_length(xpath('//yr[text()=2004]', record), 1) > 0;