Using FLWOR
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