--XML data sample to be genereated
<?xml version="1.0"?>
-<serialization SerializationRoot="SBOInboundBatchCO">
-<SBOInboundBatchCO ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchCO">
-<sboInboundBatchIO ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchIO">
<creationDate Type="DATE">2017/05/02</creationDate>
<externalReferenceDate Type="DATE">2017/05/02</externalReferenceDate>
-<sboInboundMessageIOs ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO" IsIndexed="1">
-<element ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO">
<billingPeriodsequenceNumber>2</billingPeriodsequenceNumber>
<messageCodeBehaviourCode Type="STRING">CACISBOACKGMENT</messageCodeBehaviourCode>
<messageText Type="STRING"/>
<receivableGrpId Type="STRING">615466</receivableGrpId>
<responsiblePartyIdentifier Type="STRING">Y</responsiblePartyIdentifier>
</element>
</sboInboundMessageIOs>
<sboName Type="STRING">CACi BD Collections</sboName>
<totalMessageCount Type="NUMERIC">284</totalMessageCount>
</sboInboundBatchIO>
</SBOInboundBatchCO>
</serialization>
Used the following SQL script to get this output
declare @intTotMsgCount int , @xmlstr xml
set @inttotMsgCount = (Select count(Account_number) from Imp_staging_referrals_claims_FollowUp where imp_fileId = @intfileId
and uploaded = 1 and deleted = 0)
set @xmlstr=
(select 'SBOInboundBatchCO' as '@SerializationROOT',
(
select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchCO' as '@ClassName',
(
select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchIO' as '@ClassName',
'DATE' as 'creationDate/@Type',
convert(varchar(10),cast(getdate() as date) ,111) as 'creationDate',
'DATE' as 'externalReferenceDate/@Type',
convert(varchar(10),cast(getdate() as date) ,111) as 'externalReferenceDate',
(
select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO' as '@ClassName', '1' as '@IsIndexed',
(
select ' com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO' as '@ClassName',
'1' as 'billingPeriodsequenceNumber',
'STRING' as 'messageCodeBehaviourCode/@Type',
'CACISBOACKGMENT' as 'messageCodeBehaviourCode',
'STRING' as 'messageText/@Type',
'' as 'messageText',
'STRING' as 'receivableGrpId/@Type',
substring(Account_Number,2,len(Account_Number)-4) as 'receivableGrpId',
'STRING' as 'responsiblePartyIdentifier/@Type',
'Y' as 'responsiblePartyIdentifier'
from Imp_staging_referrals_claims_FollowUp
where imp_fileId = @intFileId
and Facility_Code = @intClientCode
for xml path ('element') , elements,type
)
for xml path ('sboInboundMessageIOs') , type
)
,'STRING' as 'sboName/@Type',
'CACi BD Collections' as 'sboName',
'NUMERIC' AS 'totalMessageCount/@type',
@intTotMsgCount as 'totalMessageCount'
for xml path('sboInboundBatchIO'), elements,type
)
for xml path('SBOInboundBatchCO'), type
)
for xml path ('serialization') , type
)
--AS COL_XML
select '<?xml version="1.0"?>' + cast(@xmlstr as varchar(max)) as COL_XML
No comments:
Post a Comment