Chapter 12. XML (eXtensible Markup Language) Support
Index:
DECLARE v_xml_obj xml default '<?xml version="1.1"?><foo/>'; select v_xml_obj; set v_xml_obj=xmlattributes('tom' as name); select v_xml_obj;
CREATE TABLE if not exists ademo ( item varchar(16), xmlDemo xml );
select * from xml_table ('<?xml version="1.0" encoding="ISO-8859-1"?><bookstore><book category="COOKING"> <title lang="en">Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year> <price>30.00</price></book><book category="WEB"> <title lang="en">Learning XML</title> <author>Erik T. Ray</author> <year>2003</year> <price>39.95</price></book></bookstore>','/bookstore'); select xmlcomment('hello'); select xmlcomment('<!--hello-->'); select xmlattributes('tom' as name); SELECT xmlelement(name cat); SELECT xmlelement(name cat, xmlattributes('tom' as name)); SELECT xmlelement(name cat, xmlattributes('tom' as name),'Tom is ','ca','t'); SELECT xmlelement(name "cat$tom", xmlattributes('Demo for invalid XML name' as "a&b")); SELECT xmlelement(name cat, xmlattributes('tom' as name),'Tom is ','ca','t'); SELECT xmlelement(name cat, xmlattributes('tom' as name),xmlelement(name abc), xmlcomment('test'), xmlelement(name xyz)); SELECT XMLELEMENT("test", XMLELEMENT("test1", XMLATTRIBUTES(1 "test1_attribute"),NULL), XMLELEMENT("test2", XMLATTRIBUTES('' "test2_attribute"),''), XMLELEMENT("test3", XMLATTRIBUTES(NULL "test3_attribute")),':)'); SELECT XMLELEMENT("TEST", '123', XMLELEMENT("AA", XMLATTRIBUTES('1235678' "test_attribute"), XMLELEMENT("BB", XMLATTRIBUTES('z' "attr",NULL "Go"), 'XXX'), XMLELEMENT("CC", 'XXX')), 'qwe') SELECT XMLCONCAT(XMLELEMENT("First",'John'), XMLELEMENT("Last", 'Morgan')) AS "Name"; SELECT xmlconcat('<abc/>', '<bar>foo</bar>'); SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>'); drop table if exists departments ; create table if not exists departments(id int, name varchar(24)); insert into departments (id,name) values(0,null); insert into departments (id,name) values(101,'Accounting'); drop table if exists employees; create table if not exists employees(id int auto_increment,firstName varchar(24), lastName varchar(24),salary numeric(12,2),dept_id int); insert into employees (firstName,lastName) values('Susan','Baer'); insert into employees (firstName,lastName,salary,dept_id) values('William','Mavris',20000,101); SELECT XMLCONCAT(XMLELEMENT("First", e.firstName), XMLELEMENT("Last", e.lastName)) AS "Result" FROM employees e WHERE e.id <10; SELECT XMLELEMENT("Emp", XMLATTRIBUTES(e.id AS "ID", e.lastname), XMLELEMENT("Dept", e.dept_id), XMLELEMENT("Salary", e.salary)) AS "Emp Element" from employees e WHERE e.id = 1; SELECT XMLELEMENT("Emp", XMLFOREST(e.id, e.lastname, e.salary, 'Just a demo' as comment)) "Emp Element" FROM employees e WHERE e.id =2; SELECT XMLELEMENT("Emp", XMLATTRIBUTES(employees.id, employees.lastname), XMLELEMENT("Dept", XMLATTRIBUTES(employees.dept_id,(SELECT d.name FROM departments d WHERE d.id = employees.dept_id) as "Dept_name")), XMLELEMENT("salary", employees.salary)) AS "Emp Element" FROM employees WHERE employees.id = 2; SELECT xmlforest('abc' AS foo, 123 AS bar); SELECT xmlforest(table_name, column_name) FROM information_schema.columns WHERE table_name ilike 'e%'; SELECT xmlpi(name php, 'echo "hello world";'); SELECT XMLPI(NAME "Order analysisComp", 'imported, reconfigured, disassembled?>') AS "XMLPI"; SELECT XMLPARSE(CONTENT '123 ' ) AS PO ; SELECT XMLPARSE(CONTENT '<purchaseOrder>123</purchaseOrder>'); SELECT XMLPARSE(CONTENT '123 <purchaseOrder poNo="12435"> <customerName>Tom Cat</customerName> <itemNo>9876</itemNo> </purchaseOrder>' ) AS PO ; SELECT XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>'); SELECT XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>'); SELECT XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>' as varchar); SELECT XMLSERIALIZE(CONTENT '<Owner>Tom</Owner>'); /*SELECT XMLSERIALIZE(CONTENT '<Owner>Tom/Owner>');*/ SELECT XMLELEMENT("Department", XMLAGG(XMLELEMENT("Employee", e.id||' '||e.lastname) ORDER BY lastname)) as "Dept_list" FROM employees e WHERE e.dept_id <= 300; INSERT INTO book2018 (xmlDemo) values(xmlelement(name cat, xmlattributes('tom' as name),'Tom is ','ca','t')); select xmlDemo from book2018 where xmlDemo!=null;