XML in T-SQL being translated to Oracle

Question

I couldn't get any results when I searched XML Oracle so hopefully we get some good insight here. I've got the following T-SQL:

( 
    SELECT ' ' + mpi.CpeValue
    FROM dbo.MitigationPatchInfo mpi
    WHERE mpi.MitigationPatchId = mp.Id
        AND mpi.InfoType = 'prereq'
    FOR XML PATH (''), ROOT('CPE'), TYPE
).value('/CPE[1]', 'varchar(max)')

I'm not sure what this is doing. Can someone break this down a little bit and/or offer a PL-SQL equivalent?

Cheers guys

Possible duplicate of [SQL Query to concatenate column values from multiple rows in Oracle](http://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) — Panagiotis Kanavos, Jan 14 '16 at 17:46
This is one of the techniques used to aggregate strings, similar to MySQL's GROUP_CONCAT. There are several duplicate questions like [this one](http://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) that mentions LISTAGG for 11g or ... XMLAGG for previous versions — Panagiotis Kanavos, Jan 14 '16 at 17:48

score 0 · Accepted Answer · answered Jan 14 '16 at 17:42

I will try to explain this:

We have the query

SELECT ( 
    SELECT ' ' + mpi.CpeValue
    FROM dbo.MitigationPatchInfo mpi
    WHERE mpi.MitigationPatchId = mp.Id
        AND mpi.InfoType = 'prereq'
    FOR XML PATH (''), ROOT('CPE'), TYPE
).value('/CPE[1]', 'varchar(max)')

Lets look at the inner SELECT statement first:

SELECT ' ' + mpi.CpeValue
FROM dbo.MitigationPatchInfo mpi
WHERE mpi.MitigationPatchId = mp.Id
    AND mpi.InfoType = 'prereq'

This is a standard SELECT statement with a WHERE clause. Then we have the conversion into XML:

FOR XML PATH (''), ROOT('CPE'), TYPE

The FOR XML retrieves formal results of the SELECT query as XML

The PATH ('') specifies the name of the child elements within the parent tag in this case since we pass an empty string there are no child elements within the parent <CPE></CPE> tag. For example then we whould have something like this where the results are concatinated:

<CPE>reulst1result2result3</CPE>

if we had specified the PATH as PATH('x') for example then we would have something like this:

<CPE>
  <x>reulst1</x>
  <x>result2</x>
  <x>result3</x> 
</CPE>

The ROOT('CPE') specifies the name of the root tag <CPE></CPE>

The .value('/CPE[1]', 'varchar(max)') part of the query will return the value within the 1st element of the XML as a string of the type varchar(max).

so the results <CPE>reulst1result2result3</CPE> will be returned as reulst1result2result3

I hope this helps. So in essence it is a very round about way of doing a simple concatenation in my view butI'm just guessing here without knowing the values within your table.

This isn't concatenation. It's one of the techniquest used to aggregate strings, similar to MySQL's GROUP_CONCAT. — Panagiotis Kanavos, Jan 14 '16 at 17:47
@PanagiotisKanavos interesting. I don't really have any MySQL background. I would be more interested in learning about it. — Fuzzy, Jan 14 '16 at 17:48
If you search for string aggregation on SQL Server you'll find many techniques, including this one and SQLCLR. — Panagiotis Kanavos, Jan 14 '16 at 17:50

XML in T-SQL being translated to Oracle

1 Answers1