Using sqlcmd with FOR XML returns Invalid Object name 'dbo.Table' yet Query runs in SSMS
for-xml, sql-server, sqlcmd
Solution
As you've discovered and I confirmed, that you need to specify the `-d MyDatabaseName` option.
Otherwise, you'll get the `invalid object name "Table"` error as soon as you use `FOR XML PATH`, even if you add the database name in your script `FROM` clause (viz. even `FROM Database.dbo.Table` won't circumvent the need for `-d` with `FOR XML AUTO`). Bizarre.
Re: it does not kick out a properly formatted xml file:
You need to add the `:XML ON` as the first line in your input (`-i`) file to `sqlcmd`:
:XML ON
SELECT title1,
...
Problem
This is my query: ``` SELECT title1, [precinct percent] AS [PrecinctPercent], leader, [leader percent] AS [LeaderPercent], Winner, WinningVotes, leader2, [leader2 percent] AS [Leader2Percent], Loser, LosingVotes FROM [leader].dbo.[RACE] r inner join (select rc.[race number], max(case when seqnum = 1 then [candidate num] end) as Winner, max(case when seqnum = 1 then Votes end) as WinningVotes, max(case when seqnum = 2 then [candidate num] end) as Loser, max(case when seqnum = 2 then Votes end) as LosingVotes from (select rc.*, row_number() over (partition by rc.[race number] order by votes desc) as seqnum from [leader].dbo.[RACE CANDIDATES] rc ) rc group by rc.[race number] ) rc on r.[race number] = rc.[race number] FOR XML PATH ('WQAD'), ROOT('root') ``` This query runs and produces some results I desire. I am trying to get the XML file to output as a file. I have been able to accomplish this by opening the xml in Server Management Studio, then running a VB Script to remane the temporary xml file, then move that xml to it's destination. Hey...I gotta do what I gotta do to get these results ON AIR. Through my travels I have tried to accomlish this using sqlcmd. here is what I am trying to run: ``` sqlcmd -S WQAD-SEVE\SQLEXPRESS -i C:\Users\localtv\Desktop\QUERIES\THISONE22 .sql -o C:\Users\localtv\Desktop\RESULTS236.xml ``` It executes, but when I open/edit the XML file it outputs, I get: Msg 208, Level 16, State 1, Server WQAD-SEVE\SQLEXPRESS, Line 1 Invalid object name 'dbo.RACE'. I have tried inserting [database].dbo.RACE, but still run into the same issue. I have been working on this for weeks now and am trying to figure out a way to get this to work. Also, when I try to run the command without the -o output destination, the command line echos the same error message. From the look of my query, it makes it through quite a bit before it encounter dbo.RACE. Any suggestions? Thank you in advance for your valued assistance. ***I have removed the [databasename] and .dbo. as well, still Shows RACE as an invalid object.