Developers often ask me if there is a built-in stored procedure in SQL Server 2000 that returns the table structure in XML format. If the front-end or middle-tier code passes data to the stored procedures in XML format, a table structure similar to the following is useful:
<orders> <OrderID> <OrderID/> <CustomerID> <CustomerID/> <EmployeeID> <EmployeeID/> <OrderDate> <OrderDate/> <RequiredDate> <RequiredDate/> <ShippedDate> <ShippedDate/> <ShipVia> <ShipVia/> <Freight> <Freight/> <ShipName> <ShipName/> <ShipAddress> <ShipAddress/> <ShipCity> <ShipCity/> <ShipRegion> <ShipRegion/> <ShipPostalCode> <ShipPostalCode/> <ShipCountry> <ShipCountry/> </orders>
Once developers have the XML skeleton of the table they can specify the same names for attributes and elements in the XML string they pass to the stored procedure. Doing so makes coding stored procedures using OPENXML function a breeze – you no longer have to wonder how XML attributes and elements of the front-end code map to column names in any particular table.
Unfortunately, there is no built-in procedure for returning the XML skeleton of a table; however, developing one is easy. Column names within a particular table can be obtained by querying the syscolumns system table, information_schema.columns view or by executing the sp_columns system procedure. The stored procedure I offer you in this article uses information_schema.columns since Microsoft recommends querying information_schema views instead of system tables. Note that the stored procedure accepts two parameters: table name and owner name. This is necessary because multiple owners could have a table with the same name within a single database. If you don't specify the owner name, the procedure presumes that you need the structure for a table owned by the database owner ('dbo').
CREATE PROCEDURE dbo.get_table_xml_structure ( @table_name VARCHAR(255), @owner_name VARCHAR(30) = 'dbo' ) AS SET NOCOUNT ON /* table variable to hold values */ DECLARE @temp TABLE ( string_value VARCHAR(4000) ) /* check if the table exists */ IF NOT EXISTS ( SELECT a.name FROM sysobjects a INNER JOIN sysusers b ON a.uid = b.uid AND a.type = 'u' AND a.name = @table_name AND b.name = @owner_name) BEGIN RAISERROR('incorrect table name specified, try again', 16, 1) RETURN END INSERT @temp SELECT '<' + @table_name + '>' /* append a few blank spaces to make the output readable */ INSERT @temp SELECT ' <' + column_name + '>' + ' <' + column_name + '/>' FROM information_schema.columns WHERE table_name = @table_name AND table_schema = @owner_name ORDER BY ordinal_position INSERT @temp SELECT ' ' SELECT string_value FROM @temp RETURN
Once the procedure is created you can execute it as follows:
EXEC get_table_xml_structure 'orders', 'dbo'