UFN_BATCHBBNCMEMBERSHIP_GETMEMBERS_FROMITEMLISTXML
Converts Blackbaud Internet Solutions batch membership from XML to a table.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @MEMBERSINFO | xml | IN |
Definition
Copy
CREATE function dbo.UFN_BATCHBBNCMEMBERSHIP_GETMEMBERS_FROMITEMLISTXML
(
@MEMBERSINFO xml
)
returns @R table
(
ID uniqueidentifier,
MEMBERID uniqueidentifier,
MEMBERCONSTITUENTID uniqueidentifier,
MEMBERISPRIMARY bit,
MEMBERUPDATEADDRESS bit,
MEMBERADDADDRESS bit,
MEMBERMAKEADDRESSPRIMARY bit,
MEMBERADDRESSTYPECODEID uniqueidentifier,
MEMBERBIOINFO xml,
MEMBERBIOINFOALL xml,
MEMBERPHONETYPES xml,
MEMBERADDRESSINFO xml,
MEMBERADDRESSINFOALL xml,
SEQUENCE int
)
as
begin
--I had some problems getting the generated FROMITEMLISTXML to handle the nested functions, so I wrote my own.
--There's probably a better way to do this.
insert into @R(ID, MEMBERID, MEMBERCONSTITUENTID, MEMBERISPRIMARY, MEMBERUPDATEADDRESS, MEMBERADDADDRESS, MEMBERMAKEADDRESSPRIMARY, MEMBERADDRESSTYPECODEID, SEQUENCE,
MEMBERBIOINFO, MEMBERBIOINFOALL, MEMBERPHONETYPES,
MEMBERADDRESSINFO, MEMBERADDRESSINFOALL)
select
T.c.value('ID[1]','uniqueidentifier') AS [ID],
T.c.value('MEMBERID[1]','uniqueidentifier') as [MEMBERID],
T.c.value('MEMBERCONSTITUENTID[1]','uniqueidentifier') as [MEMBERCONSTITUENTID],
T.c.value('MEMBERISPRIMARY[1]','bit') as [MEMBERISPRIMARY],
T.c.value('MEMBERUPDATEADDRESS[1]','bit') as [MEMBERUPDATEADDRESS],
T.c.value('MEMBERADDADDRESS[1]','bit') as [MEMBERADDADDRESS],
T.c.value('MEMBERMAKEADDRESSPRIMARY[1]','bit') as [MEMBERMAKEADDRESSPRIMARY],
T.c.value('MEMBERADDRESSTYPECODEID[1]','uniqueidentifier') as [MEMBERADDRESSTYPECODEID],
T.c.value('SEQUENCE[1]','int') as [SEQUENCE],
(
select
S.d.value('PROCESS[1]', 'bit') as [PROCESS],
S.d.value('FIELD[1]', 'nvarchar(max)') as [FIELD],
S.d.value('CHANGEDFROM[1]', 'nvarchar(max)') as [CHANGEDFROM],
S.d.value('NEWVALUE[1]', 'nvarchar(max)') as [NEWVALUE],
S.d.value('CURRENTVALUE[1]', 'nvarchar(max)') as [CURRENTVALUE],
S.d.value('ACTION[1]', 'tinyint') as [ACTION],
S.d.value('FIELDKEY[1]', 'nvarchar(max)') as [FIELDKEY],
S.d.value('DATATYPE[1]', 'smallint') as [DATATYPE],
S.d.value('ATTRIBUTETYPEID[1]', 'int') as [ATTRIBUTETYPEID],
S.d.value('ALLOWMULTIPLES[1]', 'bit') as [ALLOWMULTIPLES],
S.d.value('CODETABLEID[1]', 'int') as [CODETABLEID],
S.d.value('TABLEENTRYIDLIST[1]', 'nvarchar(max)') as [TABLEENTRYIDLIST]
from T.c.nodes('MEMBERBIOINFO/ITEM') S(d)
for xml raw('ITEM'),type,elements,root('MEMBERBIOINFO'),BINARY BASE64
) as [MEMBERBIOINFO],
(
select
S.d.value('PROCESS[1]', 'bit') as [PROCESS],
S.d.value('FIELD[1]', 'nvarchar(max)') as [FIELD],
S.d.value('CHANGEDFROM[1]', 'nvarchar(max)') as [CHANGEDFROM],
S.d.value('NEWVALUE[1]', 'nvarchar(max)') as [NEWVALUE],
S.d.value('CURRENTVALUE[1]', 'nvarchar(max)') as [CURRENTVALUE],
S.d.value('ACTION[1]', 'tinyint') as [ACTION],
S.d.value('FIELDKEY[1]', 'nvarchar(max)') as [FIELDKEY],
S.d.value('DATATYPE[1]', 'smallint') as [DATATYPE],
S.d.value('ATTRIBUTETYPEID[1]', 'int') as [ATTRIBUTETYPEID],
S.d.value('ALLOWMULTIPLES[1]', 'bit') as [ALLOWMULTIPLES],
S.d.value('CODETABLEID[1]', 'int') as [CODETABLEID],
S.d.value('TABLEENTRYIDLIST[1]', 'nvarchar(max)') as [TABLEENTRYIDLIST]
from T.c.nodes('MEMBERBIOINFOALL/ITEM') S(d)
for xml raw('ITEM'),type,elements,root('MEMBERBIOINFOALL'),BINARY BASE64
) as [MEMBERBIOINFOALL],
(
select
S.d.value('FIELDKEY[1]','nvarchar(max)') as [FIELDKEY],
S.d.value('PHONETYPE[1]','nvarchar(max)') as [PHONETYPE],
S.d.value('ISEMAIL[1]','bit') as [ISEMAIL]
from T.c.nodes('MEMBERPHONETYPES/ITEM') S(d)
for xml raw('ITEM'),type,elements,root('MEMBERPHONETYPES'),BINARY BASE64
) as [MEMBERPHONETYPES],
(
select
S.d.value('PROCESS[1]', 'bit') as [PROCESS],
S.d.value('FIELD[1]', 'nvarchar(max)') as [FIELD],
S.d.value('CHANGEDFROM[1]', 'nvarchar(max)') as [CHANGEDFROM],
S.d.value('NEWVALUE[1]', 'nvarchar(max)') as [NEWVALUE],
S.d.value('CURRENTVALUE[1]', 'nvarchar(max)') as [CURRENTVALUE],
S.d.value('ACTION[1]', 'tinyint') as [ACTION],
S.d.value('FIELDKEY[1]', 'nvarchar(max)') as [FIELDKEY],
S.d.value('DATATYPE[1]', 'smallint') as [DATATYPE],
S.d.value('ATTRIBUTETYPEID[1]', 'int') as [ATTRIBUTETYPEID],
S.d.value('ALLOWMULTIPLES[1]', 'bit') as [ALLOWMULTIPLES],
S.d.value('CODETABLEID[1]', 'int') as [CODETABLEID],
S.d.value('TABLEENTRYIDLIST[1]', 'nvarchar(max)') as [TABLEENTRYIDLIST]
from T.c.nodes('MEMBERADDRESSINFO/ITEM') S(d)
for xml raw('ITEM'),type,elements,root('MEMBERADDRESSINFO'),BINARY BASE64
) as [MEMBERADDRESSINFO],
(
select
S.d.value('PROCESS[1]', 'bit') as [PROCESS],
S.d.value('FIELD[1]', 'nvarchar(max)') as [FIELD],
S.d.value('CHANGEDFROM[1]', 'nvarchar(max)') as [CHANGEDFROM],
S.d.value('NEWVALUE[1]', 'nvarchar(max)') as [NEWVALUE],
S.d.value('CURRENTVALUE[1]', 'nvarchar(max)') as [CURRENTVALUE],
S.d.value('ACTION[1]', 'tinyint') as [ACTION],
S.d.value('FIELDKEY[1]', 'nvarchar(max)') as [FIELDKEY],
S.d.value('DATATYPE[1]', 'smallint') as [DATATYPE],
S.d.value('ATTRIBUTETYPEID[1]', 'int') as [ATTRIBUTETYPEID],
S.d.value('ALLOWMULTIPLES[1]', 'bit') as [ALLOWMULTIPLES],
S.d.value('CODETABLEID[1]', 'int') as [CODETABLEID],
S.d.value('TABLEENTRYIDLIST[1]', 'nvarchar(max)') as [TABLEENTRYIDLIST]
from T.c.nodes('MEMBERADDRESSINFOALL/ITEM') S(d)
for xml raw('ITEM'),type,elements,root('MEMBERADDRESSINFOALL'),BINARY BASE64
) as [MEMBERADDRESSINFOALL]
FROM @MEMBERSINFO.nodes('/MEMBERSINFO/ITEM') T(c)
update @R
set MEMBERCONSTITUENTID = null
where MEMBERCONSTITUENTID = '00000000-0000-0000-0000-000000000000';
update @R
set MEMBERADDRESSTYPECODEID = null
where MEMBERADDRESSTYPECODEID = '00000000-0000-0000-0000-000000000000';
return;
end