Re: Create XML elements from rows
| От | Thangalin |
|---|---|
| Тема | Re: Create XML elements from rows |
| Дата | |
| Msg-id | CAANrE7oxh=HaYyWUQzOa-mSLgZz6MgjmRwWgrr3c5SZKbfyTDA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Create XML elements from rows (Thangalin <thangalin@gmail.com>) |
| Список | pgsql-general |
Here is a partial solution to the problem I encountered (the full solution uses a UNION ALL and the same statement four times):
When all four instances are in place (bakeware, cookware, kitchenware, utensils), this produces the following XML code:
A simpler approach would require the following code to work:
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!
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
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/>
<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!
В списке pgsql-general по дате отправления: