--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
Showing posts with label xmlquery. Show all posts
Showing posts with label xmlquery. Show all posts
Friday, December 1, 2017
Thursday, April 21, 2016
dbmail: sending html table in the email
Declare
@xml nvarchar(max),
@body nvarchar(max)
SET @xml = CAST(( SELECT clientcode AS 'td','',right(FilenameStr,charindex('\',reverse(FilenameStr),5)-1) as 'td','', cast(importstartDate as varchar) as 'td','',cast(ImportEndDate as varchar) as 'td'
,'', totnum as 'td', '', totdups as 'td','', totloaded as 'td','', totfail as 'td', '', totskip as 'td' FROM #fileList order by HospitalCode, importstartDate FOR XML PATH('tr'),ELEMENTS) as nvarchar(MAX))
--select @xml
SET @body ='<html><body><H3> files list for date ' + @sqllasjobrundate + '-' + convert(varchar(8),getdate(),112) + ' </H3>
<table border = 1>
<tr>
<td> Hospital code</td> <td> Fine name </td> <td> Import start date </td></td> <td> Import end date </td>
<td> Total count in file </td><td> Total duplicates </td><td> Total imported </td><td> Total failed validation </td><td> Total skipped </td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMail@mycompany.com', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'mail1@mycompany.com', -- replace with your email address
@subject = 'file log Report ' ;
end
Subscribe to:
Posts (Atom)