SQL Server – Fixing Incorrect Syntax When Generating XML

sql serversql-server-2000t-sqlxml

I am trying to run below code in SQL server 2000 SP4 but getting error.

Declare @Body varchar(8000);
Declare @TableHead varchar(8000);
Declare @TableTail varchar(8000);

Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' + 
            '<style>
    td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}
    tr.even {background-color:white;}
    tr.odd {background-color:#eeeeee;}
            </style>' + 
            '</head>' + 
            '<body><table cellpadding=0 cellspacing=0 border=0>' + 
            '<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' + 
            '<td align=center><b>Product</b></td>' + 
            '<td align=center><b>Provider</b></td>' + 
            '<td align=center><b>Data Source</b></td>' + 
            '<td align=center><b>Is Linked?</b></td></tr>';

Select @Body = (Select 
        name As [TD],
        product As [TD],
        provider As [TD],
        data_source As [TD align=center],
        is_linked As [TD align=center]
    From sys.servers
    Order By is_linked, name
    For XML Raw('tr'), Elements);

Set @Body = Replace(@Body, '_x003D_', '=');
Set @Body = Replace(@Body, '_x0020_', space(1));

Select @TableHead + @Body + @TableTail;

Error: 

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'For'.

I took this code from here:

and am using it as a reference to build a new report. I have a mix of SQL Server 2000, 2005, 2008, 2012 instances in my environment. This code works on other servers but not in 2000 SP4.

Please help me fix this.

Best Answer

According to this MSDN article for using FOR XML in SQL Server 2000, "ELEMENTS Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only." So, your usage of ELEMENTS with the RAW option isn't supported in SQL Server 2000, but it does work with AUTO.

Edit:

If you were to run the query to get the list of servers in SQL Server 2000, you'd receive a message like this:

Msg 6825, Level 16, State 1, Line 1
ELEMENTS mode requires FOR XML AUTO.

That would come after you found that sys.servers doesn't exist in SQL Server 2000.

This is the query you'd need to run to get the data you want. I've updated the column names because SQL Server 2000 doesn't like duplicated column names:

SELECT
    srvname AS [TD1]
    ,srvproduct AS [TD2]
    ,providername AS [TD3]
    ,datasource AS [TD4]
    ,isremote AS [TD5]
FROM dbo.sysservers
ORDER BY
    isremote
    ,srvname
FOR XML AUTO, elements

That query works in 2000. From there, I think it's a problem with the nested query not working in 2000 as it's expected to in 2005+. If I have some extra time, I'll play with it some more.

Edit edit:

As I had suspected, but couldn't confirm, FOR XML doesn't work in a subquery in SQL Server 2000. This page has a post from Erland Sommarskog stating as much, and he directs the person who asked a similar question to this Books Online page for SQL Server 2000 where you can see the very first point states

FOR XML is not valid in subselections, whether it is in UPDATE, INSERT, or DELETE statements, a nested SELECT statement, or other statements (SELECT INTO, assignment).

So, what you're trying to do in SQL Server 2000 can't be done.

Related Question