Обсуждение: Create XML elements from rows
Hi,
Data in a table called 'equipment_group' (eg) contains various equipment categories:
I would like to use these values as elements within an XML document. Something like:
Each of these data element can have multiple objects, as shown in the following XML snippet:
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!
Data in a table called 'equipment_group' (eg) contains various equipment categories:
bakeware
cookware
kitchenware
utensils
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>
<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!
Check below link:-
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!
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!