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:

      <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/>

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

    Requires Free Membership to View

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'
/* table variable to hold values */
 string_value VARCHAR(4000)

/* check if the table 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)

 RAISERROR('incorrect table name specified, try again', 16,

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
 table_name = @table_name
 table_schema = @owner_name
ORDER BY ordinal_position

INSERT @temp

SELECT string_value FROM @temp


Once the procedure is created you can execute it as follows:

EXEC get_table_xml_structure 'orders', 'dbo'

This was first published in February 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.