?
Solved

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

Posted on 2009-02-13
2
Medium Priority
?
154 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:docstar
2 Comments
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 total points
ID: 23640142
Think it can be done, will most likely be a procedure more so than a direct query - but if you can give details on the missing table which gives the other table names then I will give it a go...
0
 

Author Comment

by:docstar
ID: 23650208
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

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question