Monday, June 24, 2019

Basic xml queries

here are the xml queries from the video.

/****************************
basic xml queries
***********************/

--create a table with an xml data type
Create table xmlTest
(
 xmltestId serial primary key,
 test xml
);

--insert some xml
Insert into xmlTest(test)
values('<person><name>Joe Smith</name><email>js@gmail.com</email><age>34</age></person>'),
('<person><name>Kelly Jones</name><email>kj@gmail.com</email><age>24</age></person>'),
('<person><name>Lila Bard</name><email>lb@gmail.com</email><age>25</age></person>');

Select * from xmltest;

Select xmlelement(name name, test) from xmltest;

--return emails
Select xpath('//email/text()', test) from xmltest;

--return ages
Select xpath('//age/text()', test)from xmltest;

--put xml tags around query results
Select xmlforest(personlastname, personemail)
from person;

 --output a table as xmlf= fragment
Select Table_to_xml('granttype',True, True, '');

--output query results as xml fragment
Select query_to_xml('Select personfirstname, personlastname, positionname
    from person
    join employee using (personkey)
    join employeeposition using (employeekey)
 join jobposition using (positionkey)'
 ,True, True, '') ;

Select table_to_xmlschema('granttype',True, True, '');

No comments:

Post a Comment