Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

New York City .NET

The author of the NYCdotNet blog is a VB.NET and SQL server developer living and working in New York City. The author seeks to promote clean, readable, efficient code in both VB and SQL from design to development, deployment, and application maintenance.

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>

Comments

Posted by luispaton on 19 October 2011

thanks, it is possible use this code to add the list of the parameters to the parameters of each report, in reporting services?

kind regards.Luis

Posted by tom.knud.1 on 8 April 2012

Try this one:

DECLARE @PersonBase TABLE

(

 PersonBaseID INT PRIMARY KEY,

 FirstName    VARCHAR(32)  NOT NULL,

 LastName     VARCHAR(32)  NOT NULL,

 Description  VARCHAR(128) NOT NULL,

 StartDate    DATETIME     NOT NULL,

 Active       BIT          NOT NULL

)

DECLARE @PersonExtend TABLE

(

 PersonBaseID INT NOT NULL,

 ExtendName   VARCHAR(16) NOT NULL,

 ExtendIndex  INT DEFAULT 0,

 ExtendType   VARCHAR(8)  NOT NULL,

 ExtendValue  VARCHAR(32) NULL,

 PRIMARY KEY (PersonBaseID, ExtendName, ExtendIndex)

)

INSERT INTO @PersonBase VALUES (1,'Jeremiah','Doh'  ,'An indispensible cog in our human apparatus.'                ,CURRENT_TIMESTAMP,1)

INSERT INTO @PersonBase VALUES (2,'Jessica' ,'Doh'  ,'A valuable contributor to the success of this organization.',CURRENT_TIMESTAMP,1)

INSERT INTO @PersonBase VALUES (3,'Joshua'  ,'Dough','An irreplaceable source of revenue.'                         ,CURRENT_TIMESTAMP,1)

INSERT INTO @PersonBase VALUES (4,'Janet'   ,'Dough','Another irreplaceable source of revenue.'                    ,CURRENT_TIMESTAMP,1)

INSERT INTO @PersonExtend VALUES (1,'EndDate'  ,0,'datetime',NULL)

INSERT INTO @PersonExtend VALUES (1,'Type'     ,0,'string','Employee')

INSERT INTO @PersonExtend VALUES (1,'Title'    ,0,'string','Manager')

INSERT INTO @PersonExtend VALUES (1,'Paygrade' ,0,'string','13')

INSERT INTO @PersonExtend VALUES (1,'Email'    ,0,'string','Jeremiah.Doh@SpeedyMail.com')

INSERT INTO @PersonExtend VALUES (1,'Bonuses'  ,1,'money','$2,501')

INSERT INTO @PersonExtend VALUES (1,'Bonuses'  ,2,'money','$2,502')

INSERT INTO @PersonExtend VALUES (1,'Bonuses'  ,3,'money','$2,503')

INSERT INTO @PersonExtend VALUES (1,'Bonuses'  ,4,'money','$2,504')

INSERT INTO @PersonExtend VALUES (1,'Address'  ,5,'string','Suite #3344')

INSERT INTO @PersonExtend VALUES (1,'Address'  ,4,'string','1234 West McFairlane Boulevard')

INSERT INTO @PersonExtend VALUES (1,'Address'  ,3,'string','Palm Gardens')

INSERT INTO @PersonExtend VALUES (1,'Address'  ,2,'string','Wyoming')

INSERT INTO @PersonExtend VALUES (1,'Address'  ,1,'string','88775-9654')

INSERT INTO @PersonExtend VALUES (1,'Phones'   ,1,'home'  ,'666-555-3211')

INSERT INTO @PersonExtend VALUES (1,'Phones'   ,2,'cell'  ,'666-555-3212')

INSERT INTO @PersonExtend VALUES (2,'EndDate'  ,0,'datetime',NULL)

INSERT INTO @PersonExtend VALUES (2,'Type'     ,0,'string','Employee')

INSERT INTO @PersonExtend VALUES (2,'Title'    ,0,'string','Executive')

INSERT INTO @PersonExtend VALUES (2,'Paygrade' ,0,'string','14')

INSERT INTO @PersonExtend VALUES (2,'Email'    ,0,'string','Jessicah.Doh@usco.com')

INSERT INTO @PersonExtend VALUES (2,'Bonuses'  ,1,'money','$3,501')

INSERT INTO @PersonExtend VALUES (2,'Bonuses'  ,2,'money','$3,502')

INSERT INTO @PersonExtend VALUES (2,'Bonuses'  ,3,'money','$3,503')

INSERT INTO @PersonExtend VALUES (2,'Bonuses'  ,4,'money','$3,504')

INSERT INTO @PersonExtend VALUES (2,'Address'  ,5,'Street' ,'1234 East Magnolia Lane')

INSERT INTO @PersonExtend VALUES (2,'Address'  ,4,'Room'   ,'Apt #7654')

INSERT INTO @PersonExtend VALUES (2,'Address'  ,3,'City'   ,'Whipperwill')

INSERT INTO @PersonExtend VALUES (2,'Address'  ,2,'StateAb','MS')

INSERT INTO @PersonExtend VALUES (2,'Address'  ,1,'ZipLong','52315-6644')

INSERT INTO @PersonExtend VALUES (2,'Phones'   ,1,'home'  ,'666-555-3221')

INSERT INTO @PersonExtend VALUES (2,'Phones'   ,2,'cell'  ,'666-555-3222')

INSERT INTO @PersonExtend VALUES (3,'EndDate'  ,0,'datetime',NULL)

INSERT INTO @PersonExtend VALUES (3,'Type'     ,0,'string','Client')

INSERT INTO @PersonExtend VALUES (3,'Email'    ,0,'string','JoshOnTheNET567@fastmail.com')

INSERT INTO @PersonExtend VALUES (3,'Phones'   ,1,'home'  ,'666-555-3231')

INSERT INTO @PersonExtend VALUES (3,'Phones'   ,2,'cell'  ,'666-555-3232')

INSERT INTO @PersonExtend VALUES (3,'Payments' ,1,'money','$501')

INSERT INTO @PersonExtend VALUES (3,'Payments' ,2,'money','$502')

INSERT INTO @PersonExtend VALUES (3,'Payments' ,3,'money','$503')

INSERT INTO @PersonExtend VALUES (3,'Payments' ,4,'money','$504')

INSERT INTO @PersonExtend VALUES (3,'Sizes'     ,1,'shoe' ,'9.5')

INSERT INTO @PersonExtend VALUES (3,'Sizes'     ,2,'hat'  ,'8')

INSERT INTO @PersonExtend VALUES (3,'Sizes'     ,3,'waste','36')

INSERT INTO @PersonExtend VALUES (3,'Sizes'     ,4,'suit' ,'Large')

INSERT INTO @PersonExtend VALUES (4,'EndDate'  ,0,'datetime',NULL)

INSERT INTO @PersonExtend VALUES (4,'Type'     ,0,'string','Client')

INSERT INTO @PersonExtend VALUES (4,'Email'    ,0,'string','Janet987@powermail.com')

INSERT INTO @PersonExtend VALUES (4,'Phones'   ,1,'home'  ,'666-555-3241')

INSERT INTO @PersonExtend VALUES (4,'Phones'   ,2,'cell'  ,'666-555-3242')

INSERT INTO @PersonExtend VALUES (4,'Payments' ,1,'money','$1,501')

INSERT INTO @PersonExtend VALUES (4,'Payments' ,2,'money','$1,502')

INSERT INTO @PersonExtend VALUES (4,'Payments' ,3,'money','$1,503')

INSERT INTO @PersonExtend VALUES (4,'Payments' ,4,'money','$1,504')

INSERT INTO @PersonExtend VALUES (4,'Sizes'     ,1,'shoe' ,'6.5')

INSERT INTO @PersonExtend VALUES (4,'Sizes'     ,2,'hat'  ,'6')

INSERT INTO @PersonExtend VALUES (4,'Sizes'     ,3,'waste','28')

INSERT INTO @PersonExtend VALUES (4,'Sizes'     ,4,'suit' ,'Medium')

SELECT PersonBaseID as [@ID],

 (

   SELECT 'FirstName' as [@Name], 'String' as [@Type], FirstName as [data()]

   FROM @PersonBase pb

   WHERE pb.PersonBaseID = r.PersonBaseID

   FOR XML PATH('Base'), TYPE

 ),

 (

   SELECT 'LastName' as [@Name], 'String' as [@Type], LastName as [data()]

   FROM @PersonBase pb

   WHERE pb.PersonBaseID = r.PersonBaseID

   FOR XML PATH('Base'), TYPE

 ),

 (

   SELECT 'Description' as [@Name], 'String' as [@Type], Description as [data()]

   FROM @PersonBase pb

   WHERE pb.PersonBaseID = r.PersonBaseID

   FOR XML PATH('Base'), TYPE

 ),

 (

   SELECT 'StartDate' as [@Name], 'DateTime' as [@Type], StartDate as [data()]

   FROM @PersonBase pb

   WHERE pb.PersonBaseID = r.PersonBaseID

   FOR XML PATH('Base'), TYPE

 ),

 (

   SELECT 'Active' as [@Name], 'boolean' as [@Type], Active as [data()]

   FROM @PersonBase pb

   WHERE pb.PersonBaseID = r.PersonBaseID

   FOR XML PATH('Base'), TYPE

 ),

 (

   SELECT ExtendName as [@Name], ExtendType as [@Type], ExtendValue as [data()]

   FROM @PersonExtend rp

   WHERE rp.PersonBaseID = r.PersonBaseID AND rp.ExtendIndex = 0

   FOR XML PATH('Extension'), TYPE

 ),

 (

   SELECT ExtendName as [@Name],

   (

     SELECT ExtendIndex-1 as [@Index], ExtendType as [@Type], ExtendValue as [data()]

     FROM @PersonExtend rpi

     WHERE rpi.PersonBaseID = r.PersonBaseID AND ExtendIndex >0 AND rpi.ExtendName = rpo.ExtendName

     ORDER BY ExtendIndex

     FOR XML PATH('Extension'), TYPE

   )

   FROM @PersonExtend rpo

   WHERE rpo.PersonBaseID = r.PersonBaseID AND ExtendIndex >0

   GROUP BY ExtendName

   FOR XML PATH('Compound'), TYPE

 )

 FROM @PersonBase r

 FOR XML PATH('Person'), ROOT('People');

Leave a Comment

Please register or log in to leave a comment.