When I read a table with HR-XML, I tap into a view
CREATE VIEW [dbo].[vwUser]
AS
WITH XMLNAMESPACES (DEFAULT 'http://ns.hr-xml.org/2007-04-15')
SELECT UserID, login, passhash,
PersonName.value('(/PersonName/GivenName)[1]', 'nvarchar(max)') AS firstname,
PersonName.value('(/PersonName/MiddleName)[1]', 'nvarchar(max)') AS middlename,
PersonName.value('(/PersonName/FamilyName)[1]', 'nvarchar(max)') AS lastname,
PersonName.value('(/PersonName/Affix)[1]', 'nvarchar(max)') AS postfix,
homepath, loginTarget,
....
Which is later SELECTED via
variables.lstCol = "UserID,login,passhash
,firstname,middlename,lastname,postfix
....
query function getOne(required string userid) output="no" access="remote" {
variables.QueryService.addParam(value = arguments.userID, cfsqltype="cf_sql_varchar");
var result = variables.QueryService.execute(sql="SELECT #variables.lstCol# FROM dbo.vwUser WHERE Deleted = 0 AND userid = ?");
if (result.getResult().recordcount == 1)
return result.getResult();
// Create blank row
var qryBlank = QueryNew(variables.lstCol);
QueryAddRow(qryBlank);
return qryBlank;
}
Is this a good approach?