Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sql XML получить полный путь  [new]
SQLPowerUser
Member

Откуда: ссылка в профиле
Сообщений: 336
Допустим, есть XML такого вида:
<row1>
  <o1>
    <v1>апельсин</v1>
    <v2>банан</v2>
  </o1>
</row1>
<row2>
  <o2>
    <v1>виноград</v1>
    <v2>груша</v2>
  </o2>
</row2>

Можно ли получить значения с полными путями? (сортировка неважна)
fullPathvalue
/row1/o1/v1апельсин
/row1/o1/v2банан
/row2/o2/v1виноград
/row2/o2/v2груша

select @@version
Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

declare @t table (x xml);
insert into @t(x) values ('
<row1>
  <o1>
    <v1>апельсин</v1>
    <v2>банан</v2>
  </o1>
</row1>'), ('
<row2>
  <o2>
    <v1>виноград</v1>
    <v2>груша</v2>
  </o2>
</row2>');


select
x2.value('local-name(..)','nvarchar(max)') [elementName],
x2.value('.','nvarchar(max)') [elementValue]
from @t t
cross apply t.x.nodes(N'//*/text()') t2(x2)

;with xm as (
  select
  cast('/' + node.value('local-name(.)', 'varchar(900)') as varchar(900)) [fullPath],
  node.query('*') [children]
  from @t
  cross apply x.nodes('*') as roots(node)
  union all
  select
  cast(xm.fullPath + '/' + node.value('local-name(.)', 'varchar(900)') as varchar(900)),
  node.query('*') [children]
  from xm
  cross apply xm.children.nodes('*') as child(node)
)
select * from xm
where cast(children as nvarchar(max)) = ''
27 фев 19, 15:51    [21820785]     Ответить | Цитировать Сообщить модератору
 Re: sql XML получить полный путь  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
SQLPowerUser, ну так совсем чутка осталось
+
declare @xml xml = 
N'<row1>
  <o1>
    <v1>апельсин</v1>
    <v2>банан</v2>
  </o1>
</row1>'
;
WITH Xml_CTE AS
(
    SELECT
        CAST('/' + node.value('fn:local-name(.)', 'varchar(100)') AS varchar(100)) AS name,
        node.query('*') AS children,
		node.value('.', 'nvarchar(max)') as vl
    FROM @xml.nodes('/*') AS roots(node)

    UNION ALL

    SELECT
        CAST(x.name + '/' + node.value('fn:local-name(.)', 'varchar(100)') AS varchar(100)),
        node.query('*') AS children,
		node.value('.', 'nvarchar(max)') as vl
    FROM Xml_CTE x
    CROSS APPLY x.children.nodes('*') AS child(node)

)
SELECT DISTINCT name, vl
FROM Xml_CTE
WHERE children.exist('/*') = 0
OPTION (MAXRECURSION 1000)
27 фев 19, 16:41    [21820856]     Ответить | Цитировать Сообщить модератору
 Re: sql XML получить полный путь  [new]
SQLPowerUser
Member

Откуда: ссылка в профиле
Сообщений: 336
buser,

спасибо! Прогнал на реальных данных, работает!
27 фев 19, 16:45    [21820866]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить