Blog Post

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 “BypassSelection” 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>

Rate

Share

Share

Rate