Nested INNER JOIN with bcp

Question

So this query works fine without the nested SELECT/INNER JOIN. Not sure what is wrong with the nested SELECT, any ideas?

So can't seem to get it to work in conjunction with bcp

SELECT @SQLCmd = + 'bcp ' + 
                         '"SELECT ''<?xml version=""1.0"" encoding=""UTF-8""?>'' + ' + 

                         ' (SELECT CardId, Initials, firstname, lastname ' +
                         '      (SELECT CardId, SetVal ' +
                         '      FROM Business_data as bd ' +
                         '      INNER JOIN Business_set as bs on bd.SetVal=bs.id ' +
                         '      WHERE bd.CardID=ic.CardID ' +
                         '      FOR XML PATH(''BD''), TYPE ' +
                         '      ) ' +
                         ' FROM IndexCards as ic' + 
                         ' FOR XML PATH(''Employee''), ELEMENTS,  ROOT(''Employees'')) "' +
                         ' queryout '  +
                   @FileName +
                   ' -w -T -S' + @@SERVERNAME

These are the tables that i'm using

Indexcards

CardId  | Initials  | firstname | lastname  |
1       | AH        | Ash       | Hart      |
2       | AL        | Alex      | Lang      |

Business_set

ID  | Val     |
1   | Media   |
2   | Tech    |

Business_data

CardId  | SetVal  | 
1       | 1       |
2       | 1       |
2       | 2       |

Answer 1

From your last question I take, that this is in database Employees.dbo. I think, that you should either fully qualify all your table's names of - better - place an USE Employees; before your select.

And I think, that Dani Mathew is right, that there is a comma missing. The sub-select is - seen form the main select - just a column to inlcude in the output.

Try it like this:

SELECT @SQLCmd = + 'bcp ' + 
                        '"USE Employees; SELECT ''<?xml version=""1.0"" encoding=""UTF-8""?>'' + ' + 

                        ' (SELECT CardId, Initials, firstname, lastname, ' +
                        '      (SELECT CardId, SetVal ' +
                        '      FROM dbo.Business_data as bd ' +
                        '      INNER JOIN dbo.Business_set as bs on bd.SetVal=bs.id ' +
                        '      WHERE bd.CardID=ic.CardID ' +
                        '      FOR XML PATH(''BD''), TYPE ' +
                        '      ) ' +
                        ' FROM dbo.IndexCards as ic' + 
                        ' FOR XML PATH(''Employee''), ELEMENTS,  ROOT(''Employees'')) "' +
                        ' queryout '  +
                @FileName +
                ' -w -T -S' + @@SERVERNAME