1. Home
  2. SQL
  3. Mastering Oracle SQL

16.3 Generating XML Documents

The first sections of this chapter focused on how to store, inspect, validate, and modify an XML document. While this is all well and good if someone has provided you with an XML document, what should you do if you need to construct an XML document from data in your database? For this purpose, Oracle has included a set of built-in SQL functions that conform to the emerging SQL/XML standard. After describing the functions individually, we will demonstrate how these functions can be used to generate a purchase order document from the data stored in the customer, cust_order, and line_item tables.

16.3.1 XMLElement( )

The XMLElement( ) function is tasked with generating XML elements. It takes as arguments an element name, a value, and an optional set of attributes. The following example generates an XML fragment consisting of data from the supplier table:

SELECT XMLElement("supplier_id", s.supplier_id) ||

  XMLElement("name", s.name) xml_fragment

FROM supplier s;



XML_FRAGMENT

-------------------------------------------------------------

<supplier_id>1</supplier_id><name>Acme Industries</name>

<supplier_id>2</supplier_id><name>Tilton Enterprises</name>

<supplier_id>3</supplier_id><name>Eastern Importers</name>

Calls to XMLElement( ) can be nested to facilitate parent/child elements. The next example builds on the previous example by wrapping the two supplier elements in a parent element called <supplier>:

SELECT XMLElement("supplier", 

  XMLElement("supplier_id", s.supplier_id) || XMLElement("name", s.name)

  ) xml_fragment

FROM supplier s;



XML_FRAGMENT

--------------------------------------------------------------------------

<supplier>

  <supplier_id>1</supplier_id><name>Acme Industries</name>

</supplier>

<supplier>

  <supplier_id>2</supplier_id><name>Tilton Enterprises</name>

</supplier>

<supplier>

  <supplier_id>3</supplier_id><name>Eastern Importers</name>

</supplier>

Please note that the three rows of output from the previous example would normally print as three lines; we have taken the liberty of adding line feeds and tabs for readability and will do so for the rest of the examples.

16.3.2 XMLAgg( )

The XMLAgg( ) function groups together sets of elements so that they can all be children to the same parent. This function is useful for working with collections or for wrapping a set of elements under a parent element. The next example builds on the previous example by wrapping the supplier elements in a root element called <supplier_list>:

SELECT XMLElement("supplier_list",

  XMLAgg(XMLElement("supplier", 

    XMLElement("supplier_id", s.supplier_id) || XMLElement("name", s.name)

  ))) xml_document

FROM supplier s;



XML_DOCUMENT

--------------------------------------------------------------------------

<supplier_list>

  <supplier>

    <supplier_id>1</supplier_id><name>Acme Industries</name>

  </supplier>

  <supplier>

    <supplier_id>2</supplier_id><name>Tilton Enterprises</name>

  </supplier>

  <supplier>

    <supplier_id>3</supplier_id><name>Eastern Importers</name>

  </supplier>

</supplier_list>

To spice things up a bit, the next example adds the part table to the FROM clause and adds a list of parts supplied by each supplier:

SELECT XMLElement("supplier_list",

  XMLAgg(XMLElement("supplier", 

    XMLElement("supplier_id", s.supplier_id) || XMLElement("name", s.name)

      || XMLElement("part_list", XMLAgg(XMLElement("part",

        XMLElement("part_nbr", p.part_nbr) || XMLElement("name", p.name))))

  ))) xml_document

FROM supplier s INNER JOIN part p

  ON s.supplier_id = p.supplier_id

GROUP BY s.supplier_id, s.name;



XML_DOCUMENT

----------------------------------------------------------------

<supplier_list>

  <supplier>

    <supplier_id>1</supplier_id><name>Acme Industries</name>

    <part_list>

      <part><part_nbr>AI5-4557</part_nbr><name>Acme Part AI5-4557</name></part>

    </part_list>

  </supplier>

  <supplier>

    <supplier_id>2</supplier_id><name>Tilton Enterprises</name>

    <part_list>

      <part><part_nbr>TZ50828</part_nbr><name>Tilton Part TZ50828</name><part>

    </part_list>

  </supplier>

  <supplier>

    <supplier_id>3</supplier_id><name>Eastern Importers</name>

    <part_list>

      <part><part_nbr>EI-T5-001</part_nbr><name>Eastern Part EI-T5-001</name></part>

    </part_list>

  </supplier>

</supplier_list>

16.3.3 XMLForest( )

The previous examples in this section have used concatenation (||) to append sibling elements together. The XMLForest( ) function takes a list of values, generates elements for each one, and concatenates them together for you:

SELECT XMLElement("supplier", 

  XMLForest(s.supplier_id, s.name)

  ) xml_fragment

FROM supplier s;



XML_FRAGMENT

------------------------------------------------------------------------

<supplier>

  <SUPPLIER_ID>1</SUPPLIER_ID><NAME>Acme Industries</NAME>

</supplier>

<supplier>

  <SUPPLIER_ID>2</SUPPLIER_ID><NAME>Tilton Enterprises</NAME>

</supplier>

<supplier>

  <SUPPLIER_ID>3</SUPPLIER_ID><NAME>Eastern Importers</NAME>

</supplier>

If you want to specify your own element names, you can optionally use the AS clause, as demonstrated by the following:

SELECT XMLElement("supplier", 

  XMLForest(s.supplier_id AS "sup_id", s.name AS "sup_name")

  ) xml_fragment

FROM supplier s;



XML_FRAGMENT

--------------------------------------------------------------------------

<supplier>

  <sup_id>1</sup_id><sup_name>Acme Industries</sup_name>

</supplier>

<supplier>

  <sup_id>2</sup_id><sup_name>Tilton Enterprises</sup_name>

</supplier>

<supplier>

  <sup_id>3</sup_id><sup_name>Eastern Importers</sup_name>

</supplier>

16.3.4 Putting It All Together

Earlier in the chapter, you saw how data from the purchase order document could be extracted and stored in various tables. Using the three built-in SQL functions described above, we will demonstrate how to recreate the XML document from the purchase_order, cust_order, line_item, and customer tables. We'll start by generating the root element and basic purchase order data from the purchase_order, cust_order, and customer tables:

SELECT XMLElement("purchase_order",

  XMLForest(c.name AS "customer_name", 

    po.customer_po_nbr AS "po_number",

    TO_CHAR(po.customer_inception_date, 'YYYY-MM-DD') 

      AS "po_date")

) purchase_order

FROM purchase_order po INNER JOIN cust_order co 

  ON po.order_nbr = co.order_nbr

  INNER JOIN customer c ON co.cust_nbr = c.cust_nbr

WHERE po.po_id = 1000;



PURCHASE_ORDER

-------------------------------------------------------------------

<purchase_order>

  <customer_name>Alpha Technologies</customer_name>

  <po_number>11257</po_number>

  <po_date>2004-01-20</po_date>

</purchase_order>

Next, we will aggregate the line item data from the line_item table:

SELECT XMLElement("purchase_order",

  XMLForest(c.name AS "customer_name", 

    po.customer_po_nbr AS "po_number",

    TO_CHAR(po.customer_inception_date, 'YYYY-MM-DD') AS "po_date",

    XMLAgg(XMLElement("item",

      XMLForest(li.part_nbr AS "part_number", li.qty AS "quantity")))

      AS "po_items")

) purchase_order

FROM purchase_order po INNER JOIN cust_order co 

  ON po.order_nbr = co.order_nbr

  INNER JOIN customer c ON co.cust_nbr = c.cust_nbr

  INNER JOIN line_item li ON co.order_nbr = li.order_nbr

WHERE po.po_id = 1000

GROUP BY po.customer_po_nbr, po.customer_inception_date, c.name;



PURCHASE_ORDER

--------------------------------------------------------------------

<purchase_order>

  <customer_name>Alpha Technologies</customer_name>

  <po_number>11257</po_number>

  <po_date>2004-01-20</po_date>

  <po_items>

    <item>

      <part_number>AI5-4557</part_number>

      <quantity>20</quantity>

    </item>

    <item>

      <part_number>EI-T5-001</part_number>

      <quantity>12</quantity>

    </item>

  </po_items>

</purchase_order>

Hopefully, this looks familiar. Although the output is satisfactory, we find the number of joins and the GROUP BY clause to be a bit excessive, so the next version of the query uses subqueries to retrieve the customer name and line items:

SELECT XMLElement("purchase_order",


  XMLForest((SELECT c.name 



    FROM customer c INNER JOIN cust_order co ON c.cust_nbr = co.cust_nbr



    WHERE co.order_nbr = po.order_nbr) AS "customer_name", 


    po.customer_po_nbr AS "po_number",

    TO_CHAR(po.customer_inception_date, 'YYYY-MM-DD') AS "po_date",


    (SELECT XMLAgg(XMLElement("item",



      XMLForest(li.part_nbr AS "part_number", li.qty AS "quantity")))



     FROM line_item li



     WHERE li.order_nbr = po.order_nbr) AS "po_items")


) purchase_order

FROM purchase_order po 

WHERE po.po_id = 1000;



PURCHASE_ORDER

-----------------------------------------------------------------------

<purchase_order>

  <customer_name>Alpha Technologies</customer_name>

  <po_number>11257</po_number>

  <po_date>2004-01-20</po_date>

  <po_items>

    <item>

      <part_number>AI5-4557</part_number>

      <quantity>20</quantity>

    </item>

    <item>

      <part_number>EI-T5-001</part_number>

      <quantity>12</quantity>

    </item>

  </po_items>

</purchase_order>



    		Mastering Oracle SQL, 2nd Edition
    		Preface
    		Chapter 1. Introduction to SQL
    		Chapter 2. The WHERE Clause
    		Chapter 3. Joins
    		Chapter 4. Group Operations
    		Chapter 5. Subqueries
    		Chapter 6. Handling Temporal Data
    		Chapter 7. Set Operations
    		Chapter 8. Hierarchical Queries
    		Chapter 9. DECODE and CASE
    		Chapter 10. Partitioning
    		Chapter 11. PL/SQL
    		Chapter 12. Objects and Collections
    		Chapter 13. Advanced Group Operations
    		Chapter 14. Advanced Analytic SQL
    		Chapter 15. SQL Best Practices
    		Chapter 16. XML
    		16.1 What Is XML?
    		16.2 Storing XML Data
    		16.3 Generating XML Documents
    		16.4 Summary
    		Chapter 17. Regular Expressions
    		Chapter 18. Model Queries
    		Appendix A. Oracle's Old Join Syntax
    		Colophon