Thread: Create XML elements from rows

Create XML elements from rows

From
Thangalin
Date:
Hi,

Data in a table called 'equipment_group'  (eg) contains various equipment categories:

bakeware
cookware
kitchenware
utensils

I would like to use these values as elements within an XML document. Something like:

xmlelement( name eg.label )

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

<equipment>
  <bakeware>
    <object min-quantity="20">ramekin</object>
    <object min-quantity="2" alias="pan">shallow baking pan</object>
  </bakeware>
  <cookware>
    <object min-quantity="1" 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>

In PostgreSQL 9.1, I do not see an XML function that allows XML element creation based on row data. It seems the row data is always used as the values for elements.

How do you create XML elements using names stored as row data?

Thank you!

Re: Create XML elements from rows

From
Raghavendra
Date:
Check below link:-



---
Regards,
Raghavendra
EnterpriseDB Corporation



On Wed, Dec 28, 2011 at 1:41 PM, Thangalin <thangalin@gmail.com> wrote:
Hi,

Data in a table called 'equipment_group'  (eg) contains various equipment categories:

bakeware
cookware
kitchenware
utensils

I would like to use these values as elements within an XML document. Something like:

xmlelement( name eg.label )

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

<equipment>
  <bakeware>
    <object min-quantity="20">ramekin</object>
    <object min-quantity="2" alias="pan">shallow baking pan</object>
  </bakeware>
  <cookware>
    <object min-quantity="1" 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>

In PostgreSQL 9.1, I do not see an XML function that allows XML element creation based on row data. It seems the row data is always used as the values for elements.

How do you create XML elements using names stored as row data?

Thank you!

Re: Create XML elements from rows

From
Thangalin
Date:
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!