asked on

TSQL Query using FOR XML EXPLICIT return Multiple Account Details

Looking for some help with TSQL syntax using FOR XML Explicit. Creating a query where each account has a contact and an invoice flag but the change is creating additional rows to first set the invoice flag to 0 then create another account element this time with the invoice flag to 1. Basically returning the same dataset with the invoice flag is the only element that changes. The query below works great expect it puts the contact and invoice flag in the same Account body versus creating 2 Account body. Any ideas of creating what I need based off the EXPECTED RESULTS XML FORMAT?

TSQL SYNTAX

select * into #tempPort
from (
select ClientAccountId
, ClientName
, ContactCode
, ContactType
, 0 as InvoiceFlag

from Table

union

select ClientAccountId
, ClientName
, ContactCode
, ContactType
, InvoiceFlag

from Table
)x

Open in new window

TSQL SYNATX FOR XML EXPLICIT

SELECT 1 as Tag,      -- this first one sets up structure only
       0 as Parent,
       NULL as [StaticName!1!StaticName],
       NULL as [Header!2!FileDate],
	NULL as [Header!2!DeleteContactsByOmission],
       NULL as [Body!3!DataType],
       NULL as [Account!4!ClientAccountId], 
       NULL as [Account!4!Name],
       NULL as [AssignContact!5!ClientContactId], 
       NULL as [AssignContact!5!InvoiceFlag],
       NULL as [Trailer!6!DataType],
       NULL as [Trailer!6!RecordCount]
            
UNION ALL      -- now setup the SubHeader line tag 2
Select 2,1, 'StaticData', convert(char(10),getdate(),101),'True',NULL,NULL,NULL,NULL,NULL,NULL,NULL from #tempPort

UNION ALL      -- Now set up the SubBody Line tag 3
Select 3,1, 'StaticData',  convert(char(10),getdate(),101),'True','Account',NULL,NULL,NULL,NULL,NULL,NULL from #tempPort

UNION ALL       -- Now set up the AccountsData Line tag 4
Select 4,3, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,NULL,NULL,NULL,NULL  from #tempPort

UNION ALL       -- Now set up the AssignContact Line tag 5
Select 5,4, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,ContactCode, InvoiceFlag,NULL,NULL from #tempPort

UNION All       -- Now set uo the SubTrailer Line tag 6
Select 6,1, 'StaticData', convert(char(10),getdate(),101),'True','Trailer',NULL,NULL,NULL,NULL,'Account',count(*) from #tempPort

order by 3,4,5,6

FOR XML Explicit

Open in new window

SELECT STATEMENT RESULTS FROM #TEMPPORT

ACTUAL RESULTS FROM XML EXPLICIT

<StaticData>
  <Header FileDate="10/15/2017" />
  <Body DataType="Account">
    <Account ClientAccountId="123456" Name="Mickey Mouse">
      <AssignContact ClientContactId="MMouse" InvoiceFlag="0" />
	  <AssignContact ClientContactId="MMouse" InvoiceFlag="1" />
    </Account>
  </Body>
  <Trailer DataType="Account" RecordCount="2" />
</StaticData>

Open in new window

EXPECTED RESULTS FROM XML EXPLICIT

<StaticData>
  <Header FileDate="10/15/2017" />
  <Body DataType="Account">
    <Account ClientAccountId="123456" Name="Mickey Mouse">
      <AssignContact ClientContactId="MMouse" InvoiceFlag="0" />
    </Account>
    <Account ClientAccountId="123456" Name="Mickey Mouse">
      <AssignContact ClientContactId="MMouse" InvoiceFlag="1" />
    </Account>
  </Body>
  <Trailer DataType="Account" RecordCount="2" />
</StaticData>

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KANEDA 0149

ASKER

Thank you Pawan, that worked! That's what happens when you decide to downgrade to the free account :(

Looks like I'll need my membership back!!!

Pawan Kumar

Welcome :). I really liked the question.