Selecting from two tables into an XML

Question

I'm trying to execute the following SQL:

SELECT * FROM Table2
INNER JOIN
    (SELECT * FROM Table1
    INNER JOIN
    (SELECT MAX(DateTime) AS LastMeasurement, MeasurementId as LastMeasurementId
    FROM Table1 GROUP BY MeasurementId) as lastMeasurement
    ON (Table1.MeasurementId = lastMeasurement.LastMeasurementId) 
    AND (Table1.DateTime = lastMeasurement.LastMeasurement)) as hLastMeasurement
ON Table2.Id = hLastMeasurement.Id
ORDER BY Table2.Id ASC

This works just fine, but because I want to get the result in XML format and had to cast the geography column, I've changed the first line to:

SELECT Name, Description, CAST(Location as nvarchar(MAX)) FROM Table2

and added this line to the end:

FOR XML RAW ('Object'), ROOT ('Objects'), ELEMENTS

Now I'm getting the XML, but with data only from Table2.
Any advice will be welcome.

what RDBMS? [Oracle's XMLAgg](http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1031169), [DB2's XMLAgg](http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0022183.htm) or [XMLGroup](http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0050588.html), ... — Aprillion, Mar 29 '12 at 08:38
Are Name, Desription and Location all in Table 2? I might be missing something but it looks like you have no columns from table 1 in your select statement, threfore they will not appear in the XML? — GarethD, Mar 29 '12 at 10:04
Yes, they are. At line #3 I'm selecting the data from table 1, adding them to the result. The thing is that the first script works just fine, it's the breaking down of the first line that did the difference. — toy4fun, Mar 29 '12 at 10:25

Java · Accepted Answer · 2012-03-29T09:22:19.877

0

Obviously , if you want to display data from other tables then you have to select these columns like this way. IO normally use MYSQL, so this is the way I normally use.But if you are using oracle or other database, then you have to select proper alias names to display custom columns that you wish.see basic example for join using sql server here

SELECT Table2.Name,Table2.Description,lastMeasurement.MeasurementId 

FROM Table2 t2
    INNER JOIN
        (SELECT * FROM Table1
        INNER JOIN
        (SELECT MAX(DateTime) AS LastMeasurement, MeasurementId as LastMeasurementId
        FROM Table1 GROUP BY MeasurementId) as lastMeasurement
        ON (Table1.MeasurementId = lastMeasurement.LastMeasurementId) 
        AND (Table1.DateTime = lastMeasurement.LastMeasurement)) as hLastMeasurement
    ON Table2.Id = hLastMeasurement.Id
    ORDER BY Table2.Id ASC

edited Mar 29 '12 at 09:22

answered Mar 29 '12 at 08:44

Java

2,451
10
48
85

I'm using SQL Server 2010. Adding the table name to each of the column doesn't helps. As I wrote, the first script works fine. The difference is in line #1, instead of just selecting everything I'm breaking it to single columns. – toy4fun Mar 29 '12 at 09:01
yes right, I have already said,I use this way for MYSQL, for sql server you have to see some examples[http://www.java2s.com/Code/SQLServer/Table-Joins/Columnaliasintablejoin.htm] – Java Mar 29 '12 at 09:05
I have edited my answer go to link , will help you.Tell me if it works for you. – Java Mar 29 '12 at 09:12
check link in my answer not in comment. – Java Mar 29 '12 at 09:21
I've taken a look at the link, it's a kind of SQL I'm not familiar with. The thing is that the first script works just fine, it's the breaking down of the first line that did the difference. I'm tryinh to figure this out. – toy4fun Mar 29 '12 at 09:30
see, you have to use alias name for each tables, and then select columns with proper alias names , you will get what you want. – Java Mar 29 '12 at 10:45
I'm still puzzle. Why do I have to use alias if I'm breaking down `*` into single columns? The first script works fine without alias at all. – toy4fun Mar 29 '12 at 12:08

Selecting from two tables into an XML

1 Answers1