Batch template selected fields

@Rob OConnell
This is what I came up with to see the selected fields…
select XML.node.value('@FieldID','nvarchar(max)')
from BATCHTEMPLATE BT
CROSS APPLY BT.FORMDEFINITIONXML.nodes('ns:FormMetaData/ns:FormFields/ns:FormField') AS XML(node)
where BT.ID = <BATCHTEMPLATEID>

@Rob OConnell

That query seemed to work for my needs. I was just looking to see if one field existed, not display all selected fields - I guess that's the difference. The XML does seem to have much more than what is selected.

I had created a new Address Field for the Constituent Update Batch as an extension. During the import process, I was able to use that query to see if the new address field existed in the batch template during import - if it wasn't in the template, I could ignore import processing I was doing for the new Address Field. Here is the full version:

ALTER function [dbo].[UFN_FIELDEXISTSINIMPORTBATCHTEMPLTE](
@FIELDNAME nvarchar(255),
@IMPORTID uniqueidentifier

)
returns bit
with execute as caller
as begin
declare @FIELD as varchar(255);
declare @DOESFIELDEXISTS bit = 0 ;
WITH xmlnamespaces ('bb_appfx_commontypes' AS [ns])
select top 1 @FIELD = XML.node.value('@FieldID','nvarchar(max)')
from BATCHTEMPLATE BT
inner join IMPORTPROCESS IMP on IMP.BATCHTEMPLATEID = BT.ID
CROSS APPLY BT.FORMDEFINITIONXML.nodes('ns:FormMetaData/ns:FormFields/ns:FormField') AS XML(node)
where IMP.ID = @IMPORTID
and XML.node.value('@FieldID','nvarchar(max)') = @FIELDNAME
if COALESCE(@FIELD, '') <> ''
set @DOESFIELDEXISTS = 1
else
set @DOESFIELDEXISTS = 0
return @DOESFIELDEXISTS
end