Your First SQL Server 2000 Database

Retrieve data as XML

SQL Server 2000 supports the FOR XML clause, which we can use to easily return records as XML data. Use the following code in Query Analyzer to return all rows from our widgets table as XML elements:

USE MyDatabase1        
GO        
       
SELECT 1 AS Tag,        
NULL AS Parent,        
widgetName AS [Widget!1!Name],        
widgetPrice AS [Widget!1!Price],        
widgetAvailable AS [Widget!1!Available]        
FROM widgets        
FOR XML EXPLICIT        
GO

The XML will be returned as one record and will something look like this:

<Widget Name="Red Widget" Price="9.9500" Available="1"/>        
<Widget Name="Blue Widget" Price="14.5000" Available="1"/>        
<Widget Name="Green Widget" Price="24.9500" Available="1"/>        
...

For more detail on retrieving XML data from SQL Server, see my article "Retrieving Data as XML from SQL Server".

Conclusion

In this article we've created an SQL Server 2000 database from scratch. We used Enterprise Manager and Query Analyzer to accomplish the same goals, and we finished off by looking at some TSQL commands we can use to manipulate our database. If you've never worked with SQL Server 2000 before then hopefully this article has given you the information you need to get started!