;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2008/DataDebugger/' AS a)
SELECT
cldp.c.value(N'(a:Column/@Name)[1]', N'varchar(100)') AS ColumnName
,cldp.c.value(N'(a:MinLength)[1]', N'int') AS MinLength
,cldp.c.value(N'(a:MaxLength)[1]', N'int') AS MaxLength
,ldi.c.value(N'(a:Length)[1]', N'int') AS Length
,ldi.c.value(N'(a:Count)[1]', N'int') AS COUNT
INTO #ColumnLengthDistributionProfile
--FROM @x.nodes(N'/a:DataProfile/a:DataProfileOutput/a:Profiles[1]/a:ColumnLengthDistributionProfile[a:Column/@Name="city"]') cldp(c)
FROM @x.nodes(N'/a:DataProfile/a:DataProfileOutput/a:Profiles[1]/a:ColumnLengthDistributionProfile') cldp(c)
CROSS APPLY cldp.c.nodes('a:LengthDistribution/a:LengthDistributionItem') ldi(c)