Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Generate XML Puzzle

In this puzzle we have to generate the XML using a table’s data. We also need to add some static XML.Please check the sample input and expected output.

Sample Input

xmit_Date
2017-12-15 14:54:29.857
2017-12-16 14:54:29.857
NULL

Expected Output

--

<Message100>
  <PowerBI_948 Ref_Designator="ST">
    <BENCH Ref_D="ST01">1</BENCH>
    <Ctrl Ref_D="ST02">2</Ctrl>
  </PowerBI_948>
  <PowerBI_947>
    <XDate>Dec 15 2017  2:54PM</XDate>
    <XDate>Dec 16 2017  2:54PM</XDate>
    <XDate>Jan  1 1900 12:00AM</XDate>
  </PowerBI_947>
</Message100>

--

Script

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

--

CREATE TABLE XmlOut 
( 
	xmit_Date datetime 
)
GO

INSERT INTO XmlOut 
VALUES ( getdate() )  ,( getdate()  + 1 ), (null)
GO

--

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

--

DECLARE @x AS VARCHAR(MAX) = '<?xml version="1.0" encoding="UTF-8"?><Message100><PowerBI_948 Ref_Designator="ST"><BENCH Ref_D ="ST01">1</BENCH><Ctrl Ref_D ="ST02">2</Ctrl></PowerBI_948><PowerBI_947>'    

DECLARE @x1 AS VARCHAR(MAX) = '</PowerBI_947></Message100>'

DECLARE @y as VARCHAR(MAX) =''

SELECT @y = @y + '<XDate>' + CAST(ISNULL(xmit_Date,'1900/01/01') AS VARCHAR(MAX)) + '</XDate>' + '' from XmlOut

SELECT CAST(@x + @y + @x1 AS XML)
 

--

Output

--

<Message100>
  <PowerBI_948 Ref_Designator="ST">
    <BENCH Ref_D="ST01">1</BENCH>
    <Ctrl Ref_D="ST02">2</Ctrl>
  </PowerBI_948>
  <PowerBI_947>
    <XDate>Dec 15 2017  2:54PM</XDate>
    <XDate>Dec 16 2017  2:54PM</XDate>
    <XDate>Jan  1 1900 12:00AM</XDate>
  </PowerBI_947>
</Message100>

                                            
--

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