Get SQL Server table structure in XML format

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'