Joining three views into one and then using FOR XML RAW

Question

I am trying to join 3 views like this and then output XML,

view4 equal (SELECT     *
FROM         view1
Join  
SELECT     *
FROM         view2
Join 
SELECT     *
FROM         view3)

 FOR XML RAW;

anything wrong with this query sir ? as it says wrong syntax

Answer 1

I'm not understanding the meaning of some of your code, but this is an example of legal syntax:

SELECT *
FROM view1
UNION 
SELECT *
FROM view2
UNION 
SELECT *
FROM view3
FOR XML RAW;

Note that each query should return the same number of columns and same datatypes.

UNION is an implicitly distinct operation as well. UNION ALL would return all rows from each query.

Here is an example of how you might get different tables/views into your unioned view:

CREATE VIEW myView AS
SELECT ColA, ColB, ColC
FROM view1 -- Has all the columns
UNION 
SELECT ColA, ColB, cast(ColC as varchar(50))
FROM view2 -- ColC is an int but other views have varchar(50)
UNION 
SELECT ColA, null, ColC
FROM view3 -- Has no ColB

And return XML in this fashion:

SELECT ColA, ColB, ColC
FROM myView
FOR XML RAW;

Proper join syntax might look like the following:

SELECT a.ColA, b.ColB, c.ColC
FROM view1 AS a
    JOIN view2 AS b ON a.SomeID = b.SomeID
    JOIN view3 AS c ON b.SomeOtherID = c.SomeOtherID

You can create a view and query it as XML just like the union example.