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.


<Replication>

  <Sites Status="Complete">

    <Site>

      <SubnetNames>

        <SubnetName>10.1.0.0/24</SubnetName>

      </SubnetNames>

    </Site>

  </Sites>

</Replication>



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


SELECT

    [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)

WHERE 

    [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