How to create dynamic sql statment based on single XML parameter.

Question

Hi, i need to create a dynamic SQL statement that selects fields based on an XML parameter.

Say i have a stored proc with 1 param - [@FIELDS XML] that contains field names. eg...

Field 1 = Name

Field 2 = Address etc...

..in reality there would be up to 50 fields and i only want to report on the ones in the XML parameter.

how can i make a simple statement like this work..

select
  xmlParam1,
  xmlParam2,
  ...
  xmlParamN
from TABLE

etc...

NB: the data is coming from a string array in c#.

Thanks!

Answer 1

Simply loop through the array to create the appropriate SQL query as a string, then EXEC it, for instance:

DECLARE @SQL NVARCHAR(4000)

SET @SQL='select xmlParam1, xmlParam2... from Table'

EXEC(@SQL)

Obviously, the SET @SQL part would be where you loop through the array, building the string.

EDIT

To loop through the xml, you must firstly SELECT a recordset from it, such as

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>' 

SELECT ParamValues.ID.value('.','VARCHAR(20)')
  FROM @productIds.nodes('/Products/id') as ParamValues(ID)

You must then iterate through the recordset, building your string. There are many ways to do this, such as using temp tables or cursors. Somes examples can be found here.