Как я могу получить список имен элементов от значения XML в SQL Server
У меня есть таблица со столбцом XML в SQL Server 2k8. Следующий SQL получает некоторый XML:
SELECT TOP 1 my_xml_column FROM my_table
Скажем, это возвращает меня следующий XML
<a>
<b />
<c>
<d />
<d />
<d />
</c>
</a>
То, что я хотел бы получить,
/a
/a/b
/a/c
/a/c/d
/a/e
Другими словами, как я могу заставить SQL Server говорить мне структуру моего XML?
Я могу сделать следующее для получения всех названий отдельного elemtns:
SELECT C1.query('fn:local-name(.)')
FROM my_table
CROSS APPLY my_xml_column.nodes('//*') AS T ( C1 )
Возможно, если был эквивалент "локальному имени ()", который возвратил целый путь элемента, который добьется цели?
2 ответа
Вы можете сделать это чисто с помощью XQuery и рекурсивного CTE (нет OPENXML ):
DECLARE @xml xml
SET @xml = '<a><b /><c><d /><d /><d /></c></a>';
WITH Xml_CTE AS
(
SELECT
CAST('/' + node.value('fn:local-name(.)',
'varchar(100)') AS varchar(100)) AS name,
node.query('*') AS children
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
FROM Xml_CTE x
CROSS APPLY x.children.nodes('*') AS child(node)
)
SELECT DISTINCT name
FROM Xml_CTE
OPTION (MAXRECURSION 1000)
На самом деле это не так уж много магии XQuery , но, по крайней мере, все это встроено, не требует никаких хранимых процедур, специальных разрешений и т. д.
Я подозреваю, что реализация XQuery в SQL Server не справляется с этой задачей, но вот другой способ сделать это (вдохновленный этим, адаптируйте по необходимости):
DECLARE @idoc INT, @xml XML
SET @xml = (SELECT TOP 1 my_xml_column FROM my_table)
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml;
WITH
E AS (SELECT * FROM OPENXML(@idoc,'/',3)),
P AS
(
-- anchor member
SELECT id, parentid, localname AS [Path]
FROM E WHERE parentid IS NULL
UNION ALL
-- recursive member
SELECT E.id, E.parentid, P.[Path] + '/' + localname AS [Path]
FROM P INNER JOIN E ON E.parentid = P.id
)
SELECT [Path] FROM P
EXEC sp_xml_removedocument @idoc