Certain data may be stored in multiple XML elements; however, you may wish to display these within a single row within a report. This can be accomplished using FLWOR.

In the following example, the names of the subnets assigned to an Active Directory site are stored in individual XML elements.


  <Sites Status="Complete">








To obtain this information, use FLWOR to read each SubnetName in SubnetNames and concatenate these values with a semicolon:


    [dbo].[ItemCore].[ItemID] AS [ItemID],

    [dbo].[ItemCore].[Name] AS [Domain Name],

    '~/images/tables/ActiveDirectoryDomain/ReplicationSite.png' AS [ImageUrl],

    [dbo].[GetItemParentCustomerName]([dbo].[ItemCore].[ItemID]) AS [CustomerName],

    CAST([ReplicationSite].query('for $SubnetName in (SubnetNames/SubnetName) return concat(data($SubnetName), ";")') AS NVARCHAR(MAX)) AS [Subnet Names]

    FROM [dbo].[ActiveDirectoryDomains]

    INNER JOIN [dbo].[ItemCore] ON [dbo].[ItemCore].[ItemID] = [dbo].[ActiveDirectoryDomains].[ItemID]

    CROSS APPLY [ActiveDirectoryDomains].[Replication].nodes('/Replication/Sites/Site') AS R(ReplicationSite)


    [dbo].[ItemCore].[ItemDeletedDate] IS NULL

    AND [dbo].[ItemCore].[DecommissionDate] IS NULL

The reporting system automatically interprets these as a list and removes the semicolons, and displays each value on a new line within the subnets column of the report