declare @xml xml =
'<root>
<row>1</row>
<row>2</row>
<row>3</row>
</root>'


;with cte as (
	select 
		lvl	=1
		,val	=t.c.value('text()[1]', 'int') 
		,xml	=@xml 
	from @xml.nodes('/root/row[1]') as t(c)

	union all

	select
		cte.lvl + 1
		,t.c.value('text()[1]', 'int')
		,cte.xml
	from cte cross apply cte.xml.nodes('/root/row[sql:column("lvl")+1]') as t(c)
)
select lvl, val from cte

select
 count(*) as Всего, 
 count(a.n.value('text()[1]','varchar(100)')) as Заполнено
from
 @xml.nodes('/item/*') a(n);

declare @xml xml =
'<item>
<F_Partner_Types title="Тип потребителя" type="value">1</F_Partner_Types>
<C_Subscr_Code title="Лицевой счет" type="value">602060007031</C_Subscr_Code>
<N_Own_Subscr_Not_Fill title="Отказ от заполнения" type="value"/>
<N_Longitude title="Долгота" type="value">45.05138072</N_Longitude>
</item>'

;with cte as (
	select 
		lvl	=1
		,name	=t.c.value('local-name(.)', 'varchar(100)') 
		,val	=t.c.value('text()[1]', 'varchar(100)') 
		,xml	=@xml 
	from @xml.nodes('/item/*[1]') as t(c)

	union all

	select
		cte.lvl + 1
		,t.c.value('local-name(.)', 'varchar(100)') 
		,t.c.value('text()[1]', 'varchar(100)')
		,cte.xml
	from cte cross apply cte.xml.nodes('/item/*[sql:column("lvl")+1]') as t(c)
)
select lvl, name, val from cte

select 
 a.n.query('.'),
 row_number() over (order by a.n),
 a.n.value('local-name(.)', 'varchar(100)'),
 a.n.value('text()[1]', 'varchar(100)')
from
 @xml.nodes('/item/*') a(n);