SQL XML - Working with tables with unknown columns Articles, samples and tutorials

SQL XML enables you to convert the from a stored procedure/user defined function into XML format. Using this functionality, you can work with stored procedures or functions without needing to know the table column information being returned.

User defined functions

Here is a sample query which will execute the function and convert the table returned into a XML format,


DECLARE @Data XML
SET @Data = (SELECT * FROM dbo.yourFunctionName() FOR XML RAW(''Row''),ROOT(''Root''))

Assuming, yourFunctionName return a table with 3 columns i.e. Name, Age and Gender, the resulting output will be,


<Root>
  <Row Namel="ABitSmart" Age="27" Gender="Male" />
</Root>

Stored procedure

With a stored procedure it is little bit tricky. The best way I figured out is, having the stored procedure return a XML parameter as an output. The XML parameter can be created in the same way as we did for the function.

To convert a table (temporary or in memory) into XML,


-- converting a temporary table to XML format
DECLARE @Data XML
Set @Data = (Select * from  #tempTable For XML RAW('Row'),ROOT('Root'))


-- converting an in-memory table to XML format
DECLARE @Data XML
Set @Data = (Select * from  @tempTable For XML RAW('Row'),ROOT('Root'))

The above approach shows that we do not have to bother with the column details of table being returned. The XML will have all this metadata. Every node of the XML will corresponds to a table row. Each row will have the corresponding column value pair as the attribute value.

I have used this concept in creating a generic reporting architecture. There is one main executing stored procedure which takes in the procedure name and returns a XML output. LINQ calls this procedure, passing the stored procedure name. The main procedure executes(Dynamic SQL) the passed in stored procedure name (which is a reporting procedure) and converts the output table into a XML output. This XML output is returned to the main application.
The advantage is a loosely coupled architecture. The report procedures can be altered without having the application to be involved.

Have fun.

Post Comment:

Notify me by email when others post comments to this article.

Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.

No HTML formatting and links to other web sites are allowed.

This is a strictly moderated site. Absolutely no spam allowed.

Name:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.

Online Members

SQL XML - Working with tables with unknown columns

User defined functions

Stored procedure

Post Comment:

Email subscription