asked on

Need to obtain a single XML String for a set of tables

I am attempting to convert our current data schema from having "Custom Field" data in multiple tables to a single field. The problem I have is that these multiple tables are not statically named. Here is an example
CUSTOMER 1: Has 4 custom fields so there are 5 tables involved:
Customfields - id, field name, table name, field type
cf_InvoiceNum - id, docID, lValue
cf_CheckNum - id, docID, lValue
cf_CheckDate - id, docID, dtValue
cf_CustName - id, docID, sValue
CUSTOMER 2: Has 2 Custom fields so there are 3 tables involved:
Customfields - id, field name, table name, field type
cf_MLSNum - id, docID, lValue
cf_SaleStatus - id, docID, sValue

The Customfields table is static and will be present on all systems, it contains the table names of the custom fields. The ultimate goal is to get All of the data per docID in each of the customfield tabled into a single XML string that defined the field name, Value (l (int), s (string), or dt (datetime)), and datatype.
In customer 2's case it would be something like
<CustomFields>
<MLSNumber>
<Value>123456</Value>
<DataType>Int</DataType>
</MLSNumber>
<MLSNumber>
<Value>222222</Value>
<DataType>Int</DataType>
</MLSNumber>
<SalesStatus>
<Value>Sale Pending</Value>
<DataType>Varchar(40)</DataType>
</SalesStatus>
</CustomFields>

the above would be per docID. The main problem I am having is how do I create a function that can take a docID and find all of its customfield values and return a single XML string when the table names are not static, the are stored in a table but do I have to implement a cursor to do a dynamic sql for each row returned in the cursor? It can be done just seems wrong.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

docstar

ASKER

So I gave this a go using a cursor. the problem is that I cannot put it into a function because you cannot use sp_ExecuteSQL within a function. I put this in a stored procedure but then I cannot join these values with any other data I need. I can simply modify what the stored procedure returns but then I have to create a procedure for every set of data I want back.

/****************************************************************************
**
**    DSConvertCustomFieldsToXML          Function
**
**    Returns all custom field data for a given document in an XML stream.
**    
**  02/16/09 mpv  Created
**    
****************************************************************************/
ALTER FUNCTION [DSUSER].[DSConvertCustomFieldsToXML] (@DOCID AS INT)
RETURNS XML
AS
BEGIN
      DECLARE TablesCursor CURSOR FOR (SELECT sNAME, sTABLENAME, lFIELDTYPE, lFIELDLENGTH 
                                                       FROM DSUSER.tblCUSTOMFIELD)
      DECLARE @TableName VARCHAR(50);
      DECLARE @CFNAME VARCHAR(40);
      DECLARE @FIELDTYPE VARCHAR(10);
      DECLARE @FIELDLENGTH VARCHAR(10);
      DECLARE @FIELDNAME VARCHAR(40);
      DECLARE @SQLString nvarchar(500);
      DECLARE @ParmDefinition nvarchar(500);
      DECLARE @CFDATA XML;
      DECLARE @XMLReturn VARCHAR(MAX);
      SET @XMLReturn = '<CUSTOMFIELDS>'
      SET @ParmDefinition = N'@ldocID INT, @CFDATAOUT XML OUTPUT';
      OPEN TablesCursor;
      FETCH NEXT FROM TablesCursor INTO @CFNAME, @TableName, @FIELDTYPE, @FIELDLENGTH;
      WHILE @@FETCH_STATUS = 0
      BEGIN
            SET @FIELDNAME =  CASE @FIELDTYPE
                                                WHEN 3 THEN 'lVALUE'
                                                WHEN 7 THEN 'dtVALUE'
                                                WHEN 202 THEN 'sVALUE'
                                          END;
            SET @SQLString = N'SET @CFDATAOUT = (
                        SELECT      ''' + @CFNAME + ''' as "@name", 
                                    ''' + @FIELDTYPE + ''' as "@type", 
                                    ''' + @FIELDLENGTH + ''' as "@length", 
                                    ' + @FIELDNAME + ' as "@value" 
                        FROM DSUSER.' + @TableName + '
                        WHERE lDOCID = @ldocID
                        FOR XML PATH(''field'') )'                      
            EXECUTE sp_executesql
                  @SQLString
                  ,@ParmDefinition
                  ,@ldocID = @DOCID
                  ,@CFDATAOUT = @CFDATA OUTPUT;    
            SET @XMLReturn = ISNULL(@XMLReturn, '') + ISNULL(CAST(@CFDATA as VARCHAR(MAX)), '');
            FETCH NEXT FROM TablesCursor INTO @CFNAME, @TableName, @FIELDTYPE, @FIELDLENGTH;
      END;
      CLOSE TablesCursor;
      DEALLOCATE TablesCursor;
      SET @XMLReturn = @XMLReturn + '</CUSTOMFIELDS>';
      RETURN CAST(@XMLReturn as XML);
END

Open in new window