Return XML from SQL Server with particular format

Question

Post reply

Return XML from SQL Server with particular format

User7766

SSCommitted

Points: 1969
More actions
June 20, 2016 at 12:15 am

#327649

Hi All,
I'm trying to build XSL FO for PDF creation using FOP.NET. For that i need to generate XML in the below way to easily map my XML to the XSLT XSL FO. Below is the desired format that i specifically require and not able to build it properly. I'm posting my Query along with the desired o/p, can anyone please help me on this?
SELECT (SELECT u.Name, ul.UsrName, lo.Remark, lo.UsrIPAddress, lo.CreatedDate, lo.AppID, lo.AppVersion, ul.[UsrLastLoginDate]
FOR XML PATH('Columns'), TYPE)
FROM BOS_LoginType AS bo
INNER JOIN LOG_UsrAccess AS lo
INNER JOIN usrlist ul ON ul.UsrID=lo.UsrID
INNER JOIN MstPrivateDtl u on u.TypeValue= lo.UsrID AND u.Type='U'
ON CONVERT(INT, bo.Id) & lo.LogType <> 0 AND bo.Description='Login'
GROUP BY u.Name,ul.UsrName,lo.Remark,lo.UsrIPAddress, lo.CreatedDate, lo.AppID, lo.AppVersion, ul.[UsrLastLoginDate]
FOR XML RAW('ColumnHeader'), ROOT ('ReportData'), ELEMENTS;
Desired output format:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ReportData>
<ColumnHeaders>
<ColumnHeader>
<Name>Col-Header1</Name>
</ColumnHeader>
<ColumnHeader>
<Name>Col-Header2</Name>
</ColumnHeader>
<ColumnHeader>
<Name>Col-Header3</Name>
</ColumnHeader>
</ColumnHeaders>
<Rows>
<Row>
<Column>Row1-Col1</Column>
<Column>Row1-Col2</Column>
<Column>Row1-Col3</Column>
</Row>
<Row>
<Column>Row2-Col1</Column>
<Column>Row2-Col2</Column>
<Column>Row2-Col3</Column>
</Row>
</Rows>
</ReportData>
Thanks,
Pavan

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

durga.palepu Hall of Fame Points: 3530 More actions · Answer 1

Can you post the structure of the tables used in query along with some sample data to help you better?

User7766 SSCommitted Points: 1969 More actions · Answer 2

Hi,

Thanks for the reply, I cannot provide the table schema due to some VM problems here. Can you please join 2 tables with sample data to get results in the above format? Waiting for your reply..

Thanks,

PAvan

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

Headers should be created manually.

WITH t AS ( -- sample data

SELECT 1 AS a

, 2 AS b

, 3 AS c

, 4 AS d

, NULL AS z

UNION ALL

SELECT 10 AS a

, 20 AS b

, 30 AS c

, 40 AS d

, 100 AS z

)

SELECT

(SELECT cn AS [ColumnHeader/Name]

FROM (

-- create headers by hand

VALUES ( 'a' )

, ( 'b' )

, ( 'c' )

, ( 'd' )

, ( 'z' )

) headres(cn)

FOR XML path(''),Type) AS [ColumnHeaders] ,

(SELECT

(SELECT a AS [Column] FOR XML path(''),Type),

(SELECT b AS [Column] FOR XML path(''),Type),

(SELECT c AS [Column] FOR XML path(''),Type),

(SELECT d AS [Column] FOR XML path(''),Type),

-- mind nullable columns

(SELECT z AS [Column] FOR XML path(''),elements xsinil,Type)

FROM t

FOR XML path('Row'),Type) AS [Rows]

FOR XML path('ReportData'),Type

User7766 SSCommitted Points: 1969 More actions · Answer 4

The above solution works, I've replaced the sample data with my own query to get the desired result.. I actually want to construct XSL FO with this XML and create dynamic columns in the FO stylesheet..

Many thanks for your help..

Thanks,

User7766 SSCommitted Points: 1969 More actions · Answer 5

Hi,

How can i get null row values in the below format? When i tried, I'm not getting the NULL value rows in the XML, they are being sipped.. I need the output like below..

<ReportData>

<Reports>

<Name>123</Name>

<IPAddress>0.0.0.0</IPAddress>

<DateCreated>2016-02-24T18:32:11.803</DateCreated>

<ID>OS</ID>

<Version>0.0.3.0</Version>

<LastLoggedin>2016-06-23T14:12:49.043</LastLoggedin>

</Reports>

<Reports>

<Name>123</Name>

<IPAddress>0.0.0.0</IPAddress>

<DateCreated>2016-02-24T18:32:11.803</DateCreated>

<ID>OS</ID>

<Version>0.0.3.0</Version>

<LastLoggedin>2016-06-23T14:12:49.043</LastLoggedin>

</Reports>

</ReportData>

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 6

You may force generation XML elements for NULL values with the XSINIL parameter https://msdn.microsoft.com/en-us/library/ms178079.aspx

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as xsi)

SELECT a,z

FROM ( -- sample data

SELECT 1 AS a, NULL AS z

UNION ALL

SELECT 10 AS a, 100 AS z

) t

FOR XML PATH('report'), ELEMENTS XSINIL, TYPE;

WITH XMLNAMESPACES is optional, just to move xlmns:xsi="http://www.w3.org/2001/XMLSchema-instance" declaration to the document element.

Sergiy SSC Guru Points: 110209 More actions · Answer 7

maruthipuligandla (6/23/2016)
Hi,
How can i get null row values in the below format? When i tried, I'm not getting the NULL value rows in the XML, they are being sipped.. I need the output like below..
<ReportData>
<Reports>
<Name>123</Name>
<IPAddress>0.0.0.0</IPAddress>
<DateCreated>2016-02-24T18:32:11.803</DateCreated>
<ID>OS</ID>
<Version>0.0.3.0</Version>
<LastLoggedin>2016-06-23T14:12:49.043</LastLoggedin>
</Reports>
<Reports>
<Name>123</Name>
<IPAddress>0.0.0.0</IPAddress>
<DateCreated>2016-02-24T18:32:11.803</DateCreated>
<ID>OS</ID>
<Version>0.0.3.0</Version>
<LastLoggedin>2016-06-23T14:12:49.043</LastLoggedin>
</Reports>
</ReportData>

Which values in this example represent NULL's?

_____________
Code for TallyGenerator

User7766 SSCommitted Points: 1969 More actions · Answer 8

Hi,

How can i get the below format, I need to add a Header, Footer to the existing XML .

<Reports>

<Header>

<PrintedDate>23/01/2001</PrintedDate>

<PrintedBy>XYZ </PrintedBy>

</Header>

<Footer>

<DocumentPreparedBy>ABC</DocumentPreparedBy>

</Footer>

<Report>

<Name>123</Name>

<UserName></UserName>

<Remarks></Remarks>

<IPAddress>0.0.0.0</IPAddress>

<DateCreated>2016-02-24T18:32:11.803</DateCreated>

<AppID>BOS</AppID>

<AppVersion>0.0.3.0</AppVersion>

<LastLoggedin>2016-06-23T16:04:07.167</LastLoggedin>

</Report>

</Reports>

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 9

This quick suggestion should get you started

USE tempdb;

SET NOCOUNT ON;

;WITH SAMPLE_DATA(PrintedDate,PrintedBy,DocumentPreparedBy,Name,UserName,Remarks,IPAddress,DateCreated,AppID,AppVersion,LastLoggedin) AS

(SELECT * FROM

(VALUES

(

CONVERT(DATE,'20010123',112)

,'XYZ'

,'ABC'

,'123'

,''

,'0.0.0.0'

,CONVERT(DATETIME,'20160224 18:32:11.803',112)

,'BOS'

,'0.0.3.0'

,CONVERT(DATETIME,'20160623 16:04:07.167',112)

)

) AS X(PrintedDate,PrintedBy,DocumentPreparedBy,Name,UserName,Remarks,IPAddress,DateCreated,AppID,AppVersion,LastLoggedin))

SELECT

SD.PrintedDate AS 'Header/PrintedDate'

,SD.PrintedBy AS 'Header/PrintedBy'

,SD.DocumentPreparedBy AS 'Footer/DocumentPreparedBy'

,SD.Name AS 'Report/Name'

,SD.UserName AS 'Report/UserName'

,SD.Remarks AS 'Report/Remarks'

,SD.IPAddress AS 'Report/IPAddress'

,SD.DateCreated AS 'Report/DateCreated'

,SD.AppID AS 'Report/AppID'

,SD.AppVersion AS 'Report/AppVersion'

,SD.LastLoggedin AS 'Report/LastLoggedin'

FROM SAMPLE_DATA SD

FOR XML PATH(''),ROOT('Reports');

Output

<Reports>

<Header>

<PrintedDate>2001-01-23</PrintedDate>

<PrintedBy>XYZ</PrintedBy>

</Header>

<Footer>

<DocumentPreparedBy>ABC</DocumentPreparedBy>

</Footer>

<Report>

<Name>123</Name>

<UserName></UserName>

<Remarks></Remarks>

<IPAddress>0.0.0.0</IPAddress>

<DateCreated>2016-02-24T18:32:11.803</DateCreated>

<AppID>BOS</AppID>

<AppVersion>0.0.3.0</AppVersion>

<LastLoggedin>2016-06-23T16:04:07.167</LastLoggedin>

</Report>

</Reports>

User7766 SSCommitted Points: 1969 More actions · Answer 10

Hi,

When i executed this with my query, It is giving me the following output:

<Reports>

<Report>

<Header>

<PrintedDate></PrintedDate>

<PrintedBy></PrintedBy>

</Header>

<Footer>

<DocumentPreparedBy></DocumentPreparedBy>

</Footer>

<Report>

<Name>CECILIA</Name>

<UserName></UserName>

<Remarks></Remarks>

<IPAddress>121.121.15.250</IPAddress>

<DateCreated>2016-05-20T11:15:05.420</DateCreated>

<AppID>BOS</AppID>

<AppVersion>0.0.7.0</AppVersion>

<LastLoggedin>2016-06-30T11:49:01.053</LastLoggedin>

</Report>

<Report>

<Header>

<PrintedDate></PrintedDate>

<PrintedBy></PrintedBy>

</Header>

<Footer>

<DocumentPreparedBy></DocumentPreparedBy>

</Footer>

<Report>

<Name>CECILIA</Name>

<UserName></UserName>

<Remarks></Remarks>

<IPAddress>121.121.15.250</IPAddress>

<DateCreated>2016-06-01T11:43:19.480</DateCreated>

<AppID>BOS</AppID>

<AppVersion>0.0.7.0</AppVersion>

<LastLoggedin>2016-06-30T11:49:01.053</LastLoggedin>

</Report>

</Reports>

DO you want me to post my query? PLease let me know

User7766 SSCommitted Points: 1969 More actions · Answer 11

Hi,

If i've multiple rows, then Header and Footer values are repeating for each Report row. I dont want to repeat them, I just need them as seperate attributes.

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 12

maruthipuligandla (6/30/2016)
Hi,
If i've multiple rows, then Header and Footer values are repeating for each Report row. I dont want to repeat them, I just need them as seperate attributes.

Here is a quick solution

USE tempdb;

SET NOCOUNT ON;

;WITH SAMPLE_DATA(Name,UserName,Remarks,IPAddress,DateCreated,AppID,AppVersion,LastLoggedin) AS

(SELECT * FROM

(VALUES

(

'123'

,''

,'0.0.0.0'

,CONVERT(DATETIME,'20160224 18:32:11.803',112)

,'BOS'

,'0.0.3.0'

,CONVERT(DATETIME,'20160623 16:04:07.167',112)

)

,(

'223'

,''

,'0.0.0.0'

,CONVERT(DATETIME,'20160224 12:32:11.803',112)

,'BOS'

,'0.0.4.0'

,CONVERT(DATETIME,'20160923 16:04:07.167',112)

)

) AS X(Name,UserName,Remarks,IPAddress,DateCreated,AppID,AppVersion,LastLoggedin))

,SAMPLE_HEADER_FOOTER(PrintedDate,PrintedBy,DocumentPreparedBy) AS

(SELECT * FROM

(VALUES

(

CONVERT(DATE,'20010123',112)

,'XYZ'

,'ABC'

)

) AS X(PrintedDate,PrintedBy,DocumentPreparedByn))

SELECT

SHF.PrintedDate AS 'Header/PrintedDate'

,SHF.PrintedBy AS 'Header/PrintedBy'

,SHF.DocumentPreparedBy AS 'Footer/DocumentPreparedBy'

,(SELECT

SD.Name AS 'Report/Name'

,SD.UserName AS 'Report/UserName'

,SD.Remarks AS 'Report/Remarks'

,SD.IPAddress AS 'Report/IPAddress'

,SD.DateCreated AS 'Report/DateCreated'

,SD.AppID AS 'Report/AppID'

,SD.AppVersion AS 'Report/AppVersion'

,SD.LastLoggedin AS 'Report/LastLoggedin'

FROM SAMPLE_DATA SD

FOR XML PATH(''),TYPE)

FROM SAMPLE_HEADER_FOOTER SHF

FOR XML PATH(''),ROOT('Reports');

;

Output

<Reports>

<Header>

<PrintedDate>2001-01-23</PrintedDate>

<PrintedBy>XYZ</PrintedBy>

</Header>

<Footer>

<DocumentPreparedBy>ABC</DocumentPreparedBy>

</Footer>

<Report>

<Name>123</Name>

<UserName />

<Remarks />

<IPAddress>0.0.0.0</IPAddress>

<DateCreated>2016-02-24T18:32:11.803</DateCreated>

<AppID>BOS</AppID>

<AppVersion>0.0.3.0</AppVersion>

<LastLoggedin>2016-06-23T16:04:07.167</LastLoggedin>

</Report>

<Report>

<Name>223</Name>

<UserName />

<Remarks />

<IPAddress>0.0.0.0</IPAddress>

<DateCreated>2016-02-24T12:32:11.803</DateCreated>

<AppID>BOS</AppID>

<AppVersion>0.0.4.0</AppVersion>

<LastLoggedin>2016-09-23T16:04:07.167</LastLoggedin>

</Report>

</Reports>

Return XML from SQL Server with particular format

Cookies on SQLServerCentral