Journal Chacham's Journal: Oracle: Use XML to concatenate results.
So, Oracle is a database that is supposed to help you pull results together. XML is a standard to help keep things separate (within the same document).
But if i want to concatenate some records from a TABLE, SQL falls short. We do aggregation, not concatenation (of separate records). Well, ironically, XML comes to the rescue.
In order to add separation identifiers, XML concatenates the results into one field. Well, strip the field of the identifiers, and voila, we have a concatenated result.
An example:
WITH
Some_Table
AS
(
SELECT 'Moo' The_Important_Column FROM Dual UNION ALL
SELECT 'Cow' FROM Dual UNION ALL
SELECT 'Moo' FROM Dual UNION ALL
SELECT 'Cow' FROM Dual UNION ALL
SELECT 'Moo' FROM Dual UNION ALL
SELECT 'Cow' FROM Dual
)
SELECT
REPLACE
(
REPLACE
(
XMLAgg
(
XMLElement
(
"A",
The_Important_Column
)
),
'<A>'
),
'</A>'
) Result
FROM
Some_Table;
The result is
RESULT
----------------------------------------------------------------------
MooCowMooCowMooCow
This is basic stuff.
Laurent Schneider came up with an interesting way to COUNT all records in all TABLEs via another XML option (Here):
SELECT
Table_Name,
TO_NUMBER
(
ExtractValue
(
XMLType(DBMS_XMLGen.GetXML('SELECT COUNT(*) C FROM ' || Table_Name)),
'/ROWSET/ROW/C'
)
) Total
FROM
All_Tables
WHERE
Owner = 'EHPAS_OBJ';
Oracle: Use XML to concatenate results. More Login
Oracle: Use XML to concatenate results.
Slashdot Top Deals