Create XML elements from rows

Question

Background

A table (equipment_group [eg]) includes various equipment categories:

bakeware
cookware
kitchenware
utensils

Problem

The values must be encoded XML elements, such as:

xmlelement( name eg.label )

Each element can have multiple objects, as shown in the following XML snippet:

<equipment>
  <bakeware>
    <object min-quantity="20">ramekin</object>
    <object alias="pan">shallow baking pan</object>
  </bakeware>
  <cookware>
    <object alias="pot">medium pot</object>
  </cookware>
  <utensils>
    <object alias="torch">kitchen butane torch</object>
    <object alias="sieve">fine-mesh sieve</object>
    <object alias="whisk">wire whisk</object>
  </utensils>
</equipment>

Update

At first glance, it appears as though xmlconcat can be used, however xmlconcat only allows balanced XML. That is, it does not seem possible to do the following:

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

This is well-formed XML, however xmlconcat raises an error:

ERROR:  invalid XML content
LINE 1: select xmlconcat( '<a>', '<b/>', '</a>' );
                          ^
DETAIL:  Entity: line 1: parser error : Premature end of data in tag a line 1

Question

How would you create XML elements with names that are populated using tabular data?

Thank you!

Dave Jarvis · Accepted Answer · 2011-12-28 13:08:13Z

1

Since xmlconcat only accepts well-formed XML for parameters, one option is to create a sub-query of the desired elements as follows:

  xmlelement( name "bakeware",
    xmlconcat(
      array_to_string( array_agg(
        xmlelement( name "object",
          xmlattributes( e.abridge AS "alias" ),
          e.name
        )
      ), '')::xml
    )
  )

This then concatenates all the rows (i.e., every value of <object ...>) into a single row of well-formed XML code. The row can then be concatenated using the xmlconcat function.

A downside to this solution is that each of the items will have to have a duplicate query with a hardcoded name. This is somewhat acceptable when there are only a few elements. Another downside is that if additional element types are added (e.g., tableware, glassware, or dinnerware) or renamed (e.g., utensils to cutlery), then source code will have to be modified.

edited Dec 28, 2011 at 13:08

answered Dec 28, 2011 at 9:08

Dave Jarvis

31.3k43 gold badges186 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create XML elements from rows

Background

Problem

Update

Question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background

Problem

Update

Question

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related