none
FOR XML PATH - Whats the difference between these two queries RRS feed

  • Question

  • Hello,

    I am in a situation and i have tried to create a similiar situation using northwind database. I just want to understand difference between these query.




    Query 1:

    USE NORTHWND
    
    GO
    
    
    
    select 
    
    	 FirstName as '@FirstName1'
    
    	,(
    
    		select ShipName as '@ShipName'
    
    		from
    
    			dbo.Orders ORD
    
    		where
    
    			ORD.EmployeeID = EMP.EmployeeID
    
    		FOR XML PATH('Ship'), ROOT('Ships'), Type
    
    	)
    
    	,(
    
    		select TerritoryDescription as '@TerritoryDescription'
    
    		from
    
    			dbo.EmployeeTerritories ET
    
    		INNER JOIN
    
    			dbo.Territories TT
    
    		ON
    
    			ET.TerritoryID = TT.TerritoryID
    
    		WHERE
    
    			ET.EmployeeID = EMP.EmployeeID
    
    		FOR XML PATH('EmployeeTerritort'), ROOT('EmployeeTerritories'), Type
    
    	)
    
    FROM
    
    	dbo.Employees EMP 
    
    --INNER JOIN
    
    --	dbo.Orders ORDO
    
    --ON
    
    --	ORDO.EmployeeID = EMP.EmployeeID
    
    WHERE
    
    	EMP.EmployeeID = 1
    
    FOR XML PATH('Employee'), ROOT('Employees'), TYPE







    Query 2:



    USE NORTHWND
    
    GO
    
    
    
    select 
    
    	 FirstName as '@FirstName1'
    
    	,(
    
    		select ShipName as '@ShipName'
    
    		from
    
    			dbo.Orders ORD
    
    		where
    
    			ORD.EmployeeID = EMP.EmployeeID
    
    		FOR XML PATH('Ship'), ROOT('Ships'), Type
    
    	)
    
    	,(
    
    		select TerritoryDescription as '@TerritoryDescription'
    
    		from
    
    			dbo.EmployeeTerritories ET
    
    		INNER JOIN
    
    			dbo.Territories TT
    
    		ON
    
    			ET.TerritoryID = TT.TerritoryID
    
    		WHERE
    
    			ET.EmployeeID = EMP.EmployeeID
    
    		FOR XML PATH('EmployeeTerritort'), ROOT('EmployeeTerritories'), Type
    
    	)
    
    FROM
    
    	dbo.Employees EMP 
    
    INNER JOIN
    
    	dbo.Orders ORDO
    
    ON
    
    	ORDO.EmployeeID = EMP.EmployeeID
    
    WHERE
    
    	EMP.EmployeeID = 1
    
    FOR XML PATH('Employee'), ROOT('Employees'), TYPE
    NOW, the only differnece between these two is INNER JOIN in the outer query. With inner join in the outer query generates NULL result, however, if i comment the outer inner join as in query 1 then it does generate valid xml. WHY ?

    I am unable to understand this joining issue and its implication on internal nodes.

    ARE there any limitations in TSQL to generate xml using "FOR XML PATH" as compare to other languages ?

    Can you explain the reason in detail.

    Thanks
    Think BIG but Positive, may be GLOBAL better UNIVERSAL.
    Friday, November 20, 2009 5:17 PM

Answers

  • Have you read my answer? That's all the differences between your two queries. I am not quite sure what else you are asking about. The JOIN in the query 2 returns only qualified records that both exists in emp and order tables. In query1 without the join with Order table, the records are returned if exist in emp table. The subquery will return the ShipName  if the empID exists in Order table, or return nothing if the empID not exists in Order table
    • Marked as answer by KJian_ Monday, November 30, 2009 10:05 AM
    Tuesday, November 24, 2009 6:31 PM
    Answerer

All replies

  • IF the employee with ID=1 does not have orders in dbo.Orders, the second query returns nothing. However, the first query returns the xml with empty node <Ships> because the employee with ID=1 exists in dbo.Employees.

    Friday, November 20, 2009 7:07 PM
    Answerer

  • Use default northwind DB sample and assume records in all relevant tables exists.

    Think BIG but Positive, may be GLOBAL better UNIVERSAL.
    Monday, November 23, 2009 8:57 AM
  • Hello Moderator,

    If this is not a correct place for this, can you change this to database engine section ? OR any help you can do about this ?
    Think BIG but Positive, may be GLOBAL better UNIVERSAL.
    Monday, November 23, 2009 2:35 PM
  • Have you read my answer? That's all the differences between your two queries. I am not quite sure what else you are asking about. The JOIN in the query 2 returns only qualified records that both exists in emp and order tables. In query1 without the join with Order table, the records are returned if exist in emp table. The subquery will return the ShipName  if the empID exists in Order table, or return nothing if the empID not exists in Order table
    • Marked as answer by KJian_ Monday, November 30, 2009 10:05 AM
    Tuesday, November 24, 2009 6:31 PM
    Answerer