Re: Create XML elements from rows

[Thangalin <thangalin@xxxxxxxxx>]

Here is a partial solution to the problem I encountered (the full solution uses a UNION ALL and the same statement four times):

SELECT
  xmlelement( name "bakeware",
    xmlconcat(
      array_to_string( array_agg(
        xmlelement( name "object",
          xmlattributes( e.abridge AS "alias" ),
          e.name
        )
      ), '')::xml
    )
  )
FROM
  recipe.equipment_group eg,
  recipe.equipment e
WHERE
  eg.label='bakeware' AND
  eg.id = e.equipment_group_id

When all four instances are in place (bakeware, cookware, kitchenware, utensils), this produces the following XML code:

<bakeware><object alias="pan">bread pan</object></bakeware>
<cookware/>
<kitchenware><object alias="">bowl (dry)</object><object alias="">bowl (wet)</object></kitchenware>
<utensils/>

A simpler approach would require the following code to work:

xmlconcat( '<a>', '<b/>', '</a>' );

Unfortunately, the XML parser does not recognize that that series of concatenations is well-formed XML.

Is there another approach that I am missing?

Thank you!