SQL Puzzle | The XML Explicit Puzzle

18 Wednesday Oct 2017

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

Tags

SQL Puzzle | The XML Explicit Puzzle

In this puzzle you have to generate the XML using some method. Any method like XML Explicit or XML Auto or XML path.
Please check out the sample input values and sample expected output below.

Sample Input

ClientAccountId	ClientName	ContactCode	InvoiceFlag
123456	Mickey Mouse	MMouse	0
123456	Mickey Mouse	MMouse	1

Expected Output


<StaticData>
  <Header FileDate="10/16/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>

Script

Use below script to create table and insert sample data into it.

--

;WITH tempPort AS
(
     SELECT 123456 ClientAccountId , 'Mickey Mouse' ClientName , 'MMouse' ContactCode , 0 InvoiceFlag 
     UNION ALL
     SELECT 123456 ClientAccountId , 'Mickey Mouse' ClientName , 'MMouse' ContactCode , 1 InvoiceFlag      
)
SELECT * FROM tempPort

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


--

;WITH tempPort AS
(
     SELECT 123456 ClientAccountId , 'Mickey Mouse' ClientName , 'MMouse' ContactCode , 0 InvoiceFlag 
     UNION ALL
     SELECT 123456 ClientAccountId , 'Mickey Mouse' ClientName , 'MMouse' ContactCode , 1 InvoiceFlag      
)
SELECT Tag,Parent,[StaticData!1!StaticData],
                        [Header!2!FileDate],
                        [Header!2!DeleteContactsByOmission],
                        [Body!3!DataType],
                        [Account!4!ClientAccountId], 
                        [Account!4!Name],
                        [AssignContact!5!ClientContactId], 
                        [AssignContact!5!InvoiceFlag],        
                        [Trailer!6!DataType],
                        [Trailer!6!RecordCount]
FROM 
(
     SELECT 1 as Tag,  
              0 as Parent,
              0 child,             
              NULL as [StaticData!1!StaticData],
              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   
     Select 2,1,0, 'StaticData', convert(char(10),getdate(),101),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

     UNION ALL   
     Select 3,1,0, 'StaticData',  convert(char(10),getdate(),101),'True','Account',NULL,NULL,NULL,NULL,NULL,NULL

     UNION ALL  
           
           Select 4,3,0, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,NULL,NULL,NULL,NULL from ( SELECT DISTINCT ClientAccountId,ClientName FROM tempPort t ) t
           UNION ALL
           Select 5,4,1, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,v.ContactCode,v.InvoiceFlag,NULL,NULL from ( SELECT DISTINCT ClientAccountId,ClientName FROM tempPort t ) t
           CROSS APPLY
           (
                SELECT TOP 1 InvoiceFlag,ContactCode
                FROM tempPort t1
                WHERE t1.ClientAccountId = t.ClientAccountId
                AND t1.ClientName = t1.ClientName 
           )v

     UNION ALL   
           Select 4,3,2, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,NULL,NULL,NULL,NULL from ( SELECT DISTINCT ClientAccountId,ClientName FROM tempPort t ) t
           UNION ALL
           Select 5,4,3, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,v.ContactCode,v.InvoiceFlag,NULL,NULL from ( SELECT DISTINCT ClientAccountId,ClientName FROM tempPort t ) t
           CROSS APPLY
           (
                SELECT TOP 1 InvoiceFlag,ContactCode
                FROM tempPort t1
                WHERE t1.ClientAccountId = t.ClientAccountId
                AND t1.ClientName = t1.ClientName 
                ORDER BY InvoiceFlag DESC
           )v

     UNION All   
     Select 6,1,0 ,'StaticData', convert(char(10),getdate(),101),'True','Trailer',NULL,NULL,NULL,NULL,'Account',count(*) from tempPort
)r
ORDER BY 3,4,5,6,child
FOR XML Explicit


--

Output

--

<StaticData>
  <Header FileDate="10/16/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>


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The 3 Decimal Digits Puzzle