I have two tables in an application that I support. One is called [Report], and the other is [ReportParameter].
--SAMPLE DATA
DECLARE @Report TABLE (
ReportID INT PRIMARY KEY,
Title VARCHAR(30),
TitleHelp VARCHAR(100),
ReportName VARCHAR(50),
ShowSearchScreen BIT,
AdminsOnly BIT
)
DECLARE @ReportParameter TABLE (
ReportID INT NOT NULL,
ParameterName VARCHAR(30) NOT NULL,
[Type] VARCHAR(15) NOT NULL,
PRIMARY KEY (ReportID, ParameterName)
)
INSERT INTO @Report VALUES
(1,'Report #1','First report...','Report1.rpt',1,0),
(2,'Report #2','Second report (no selection screen)','Report2.rpt',0,1)
INSERT INTO @ReportParameter VALUES
(1,'@StartDate','datetime'),
(1,'@EndDate','datetime'),
(2,'@ProductCode','string')
SELECT * FROM @Report
SELECT * FROM @ReportParameter
My goal is to convert these two tables into an XML file. There are a few tricky things about the destination schema:
Specific Name for the root element and all child elements.
Mixed levels of hierarchies.
Some denormalization of the data is required because the new schema uses a “tag” format rather than allowing some custom fields like my “ShowSearchScreen” field.
The first part is pretty easy. I can write this query to get the below formatted XML output.
SELECT ReportID as [@ID], Title, TitleHelp as [Description],
ReportName as [FileName]
FROM @Report r
FOR XML PATH('Report'), ROOT('Reports');
Results in:
<Reports>
<Report ID="1">
<Title>Report #1</Title>
<Description>First report...</Description>
<FileName>Report1.rpt</FileName>
</Report>
<Report ID="2">
<Title>Report #2</Title>
<Description>Second report (no selection screen)...</Description>
<FileName>Report2.rpt</FileName>
</Report>
</Reports>
Note the FOR XML PATH(‘Report’) which identifies what I want each data node to be called, and the ROOT(‘Reports’) option which lets me name the root node. So far so good. Now for adding my parameters. I need to create the Parameters node by doing a subselect, and I can get a column to show as an attribute by prefixing the name with an @ sign:
SELECT ReportID as [@ID], Title, TitleHelp as [Description],
ReportName as [FileName],
(
SELECT ParameterName as [@Name],
[Type] as [@Type]
FROM @ReportParameter rp
WHERE rp.ReportID = r.ReportID
FOR XML PATH('Parameter'), TYPE
) as [Parameters]
FROM @Report r
FOR XML PATH('Report'), ROOT('Reports');
This results in the following XML:
<Reports>
<Report ID="1">
<Title>Report #1</Title>
<Description>First report...</Description>
<FileName>Report1.rpt</FileName>
<Parameters>
<Parameter Name="@EndDate" Type="datetime" />
<Parameter Name="@StartDate" Type="datetime" />
</Parameters>
</Report>
...
</Reports>
The “TYPE” keyword tells the main query that the child query will be passing up XML nodes and that it shouldn’t try to escape it as text.
Almost there. Now I just want to expose my “ShowSearchScreen” field. However, my schema doesn’t have a field for it. Instead, the schema supports arbitrary tags with an ID=”” atrribute and node value. This means I have to take advantage of a special function called data() which I will assign as the name of the column I want to show up as the node’s value.
SELECT ReportID as [@ID], Title, TitleHelp as [Description],
ReportName as [FileName] ,
(
SELECT 'ShowSearchScreen' as [@ID],
rss.ShowSearchScreen as [data()]
FROM @Report rss where rss.ReportID = r.ReportID
FOR XML PATH('Tag'), TYPE
) as [Tags],
(
SELECT ParameterName as [@Name], [Type] as [@Type]
FROM @ReportParameter rp where rp.ReportID = r.ReportID
FOR XML PATH('Parameter'), TYPE
) as [Parameters]
from @Report r
FOR XML PATH('Report'), ROOT('Reports');
This results in our final XML document which meets our specs.
<Reports>
<Report ID="1">
<Title>Report #1</Title>
<Description>First report...</Description>
<FileName>Report1.rpt</FileName>
<Tags>
<Tag ID="ShowSearchScreen">1</Tag>
</Tags>
<Parameters>
<Parameter Name="@EndDate" Type="datetime" />
<Parameter Name="@StartDate" Type="datetime" />
</Parameters>
</Report>
...
</Reports>
Bonus tip – how to do a pivot: If you wanted to get the “AdminsOnly” field to show up as a second tag, you could extract it by changing the query to something like this:
SELECT ReportID as [@ID], Title, TitleHelp as [Description],
ReportName as [FileName] ,
(
SELECT * FROM (SELECT 'ShowSearchScreen' as [@ID],
rss.ShowSearchScreen as [data()]
FROM @Report rss where rss.ReportID = r.ReportID
UNION ALL
SELECT 'AdminsOnly' as [@ID],
rss.AdminsOnly as [data()]
FROM @Report rss where rss.ReportID = r.ReportID)
AS tags FOR XML PATH('Tag'), TYPE
) as [Tags],
(
SELECT ParameterName as [@Name], [Type] as [@Type]
FROM @ReportParameter rp where rp.ReportID = r.ReportID
FOR XML PATH('Parameter'), TYPE
) as [Parameters]
from @Report r
FOR XML PATH('Report'), ROOT('Reports');
This is a pretty standard SELECT * FROM (<my subselect query>) with the FOR XML stuff on the outside. I could add as many tags as I wished to do so by adding more UNION ALL statements. This allows for pivoting the fields into the tag node list and will generate something like this:
<Tags>
<Tag ID="ShowSearchScreen">1</Tag>
<Tag ID="AdminsOnly">0</Tag>
</Tags>
