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