{"id":73286,"date":"2012-09-05T08:00:16","date_gmt":"2012-09-05T08:00:16","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/generating-xml-from-sql-and-pl-sql-part-1\/"},"modified":"2021-07-14T13:07:51","modified_gmt":"2021-07-14T13:07:51","slug":"generating-xml-from-sql-and-pl-sql-part-1","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/generating-xml-from-sql-and-pl-sql-part-1\/","title":{"rendered":"Generating XML from SQL and PL\/SQL – Part 1: Introduction"},"content":{"rendered":"

From time to time you’ll have to represent your data stored in a database in an XML format, eg. to exchange it between systems, to send it to external parties, etc. In this introduction, I’ll show you step by step using small examples how you can easily generate XML from tables.<\/p>\n

SQL<\/h2>\n

There are some SQL functions available to generate XML. The first function is XMLElement, it creates an element for the data specified, usually coming from a table column:<\/p>\n

SELECT XMLElement( \"DEPARTMENT\"\r\n                 , department_name\r\n                 )\r\n  FROM departments\r\n WHERE department_id IN (10, 20);<\/pre>\n
Resulting in these two rows(from two records):<\/p>\n
<DEPARTMENT>Administration<\/DEPARTMENT>\r\n<DEPARTMENT>Marketing<\/DEPARTMENT><\/pre>\n
That’s the easy part, generating data surrounded with XML tags. But of course you need to add attributes to these elements. This is still easy to do with the XMLAttributes function:<\/p>\n
SELECT XMLElement(\"DEPARTMENT\"\r\n                 , XMLAttributes( department_id as \"ID\"\r\n                                )\r\n                 , department_name\r\n                 )\r\nFROM departments\r\nWHERE department_id IN (10, 20);<\/pre>\n
<DEPARTMENT ID=\"10\">Administration<\/DEPARTMENT>\r\n<DEPARTMENT ID=\"20\">Marketing<\/DEPARTMENT><\/pre>\n
OK…We now can create an XML Element with attributes, but we have more data than one element. So we add another element:<\/p>\n
SELECT XMLElement(\"DEPARTMENT_ID\"\r\n                 , department_id\r\n                 )\r\n     , XMLElement(\"DEPARTMENT\"\r\n                 , department_name\r\n                 )\r\n  FROM departments\r\n WHERE department_id IN (10, 20);<\/pre>\n
Result:<\/p>\n
<DEPARTMENT_ID>10<\/DEPARTMENT_ID>     <DEPARTMENT>Administration<\/DEPARTMENT>\r\n<DEPARTMENT_ID>20<\/DEPARTMENT_ID>     <DEPARTMENT>Marketing<\/DEPARTMENT><\/pre>\n
Oops…two columns, not really the way I wanted it, I want all XML in 1 column. We can use XMLForest for this. \u00a0It generates a forest of XML Elements, i.e. multiple XML Elements:<\/p>\n
SELECT XMLForest(department_id as \"ID\"\r\n                , department_name as \"NAME\"\r\n                )\r\n  FROM departments\r\n WHERE department_id IN (10, 20);<\/pre>\n
<ID>10<\/ID><NAME>Administration<\/NAME>\r\n<ID>20<\/ID><NAME>Marketing<\/NAME><\/pre>\n
That’s more like it, one column for each record. And by combining XML Element and XML Forest we can get this:<\/p>\n
SELECT XMLElement(\"DEPARTMENT\"\r\n                 , XMLForest(department_id as \"ID\"\r\n                            , department_name as \"NAME\"\r\n                            )\r\n                 )\r\n  FROM departments\r\n WHERE department_id IN (10, 20);<\/pre>\n
<DEPARTMENT><ID>10<\/ID><NAME>Administration<\/NAME><\/DEPARTMENT>\r\n<DEPARTMENT><ID>20<\/ID><NAME>Marketing<\/NAME><\/DEPARTMENT><\/pre>\n
An XML Element containing an XML Forest, i.e. one element containing multiple elements.<\/p>\n
Up to now, we got multiple rows back from the query. Using XMLAgg we can retrieve one row with all records from the query:<\/p>\n
SELECT XMLAgg(XMLElement(\"DEPARTMENT\"\r\n                        , XMLAttributes( department_id as \"ID\"\r\n                                       )\r\n                        , department_name\r\n                        )\r\n             )\r\n  FROM departments\r\n WHERE department_id IN (10, 20);<\/pre>\n
<DEPARTMENT ID=\"10\">Administration<\/DEPARTMENT><DEPARTMENT ID=\"20\">Marketing<\/DEPARTMENT><\/pre>\n
These are some SQL functions to generate XML and when combining these functions, one can create some nice and more complex XML Documents:<\/p>\n
SELECT XMLElement(\"DEPARTMENT\"\r\n                 , XMLAttributes( department_id as \"ID\"\r\n                                , department_name as \"NAME\"\r\n                                )\r\n                 , XMLElement(\"EMPLOYEES\"\r\n                             , (SELECT XMLAgg( XMLElement(\"EMPLOYEE\"\r\n                                                         , XMLForest(employee_id as \"ID\"\r\n                                                                    ,first_name||' '||last_name as \"NAME\"\r\n                                                                    )\r\n                                                         )\r\n                                             )\r\n                                 FROM employees emp\r\n                                WHERE emp.department_id = dept.department_id\r\n                                )\r\n                              )\r\n                )\r\n  FROM departments dept\r\n WHERE department_id IN (10, 20);<\/pre>\n
This query generates two rows, one row for each department:<\/p>\n