Thursday, January 27, 2011

Creating a deep hierarchy with FOR XML

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:
  1. Specific Name for the root element and all child elements.
  2. Mixed levels of hierarchies.
  3. 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>

No comments:

Post a Comment