How can I get a list of element names from an XML value in SQL Server
I have a table with an XML column in SQL Server 2k8. The following SQL retrieves some XML:
SELECT TOP 1 my_xml_column FROM my_table
Let's say it returns me the following XML
<a>
<b />
<c>
<d />
<d />
<d />
</c>
</a>
What I would like to get is
/a
/a/b
/a/c
/a/c/d
/a/e
In other words, how can I get SQL Server to tell me the structure of my XML?
I can do the following to ge开发者_如何学Ct all the names of the individual elemtns:
SELECT C1.query('fn:local-name(.)')
FROM my_table
CROSS APPLY my_xml_column.nodes('//*') AS T ( C1 )
Perhaps if there was an equivalent to "local-name()" that returned the whole path of the element that would do the trick?
You can do this cleanly with XQuery and a recursive CTE (no 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)
It's not really doing much XQuery magic, but at least it's all inline, doesn't require any stored procedures, special permissions, etc.
UDF for you.....
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[XMLTable](@x XML)
RETURNS TABLE
AS RETURN
WITH cte AS (
SELECT
1 AS lvl,
x.value('local-name(.)','NVARCHAR(MAX)') AS Name,
CAST(NULL AS NVARCHAR(MAX)) AS ParentName,
CAST(1 AS INT) AS ParentPosition,
CAST(N'Element' AS NVARCHAR(20)) AS NodeType,
x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,
x.value('local-name(.)','NVARCHAR(MAX)')
+ N'['
+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)
+ N']' AS XPath,
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position,
x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,
x.value('text()[1]','NVARCHAR(MAX)') AS Value,
x.query('.') AS this,
x.query('*') AS t,
CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,
CAST(1 AS INT) AS ID
FROM @x.nodes('/*') a(x)
UNION ALL
SELECT
p.lvl + 1 AS lvl,
c.value('local-name(.)','NVARCHAR(MAX)') AS Name,
CAST(p.Name AS NVARCHAR(MAX)) AS ParentName,
CAST(p.Position AS INT) AS ParentPosition,
CAST(N'Element' AS NVARCHAR(20)) AS NodeType,
CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,
CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,
ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
ORDER BY (SELECT 1)) AS Position,
CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,
CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, c.query('.') AS this,
c.query('*') AS t,
CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,
CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)
FROM cte p
CROSS APPLY p.t.nodes('*') b(c)), cte2 AS (
SELECT
lvl AS Depth,
Name AS NodeName,
ParentName,
ParentPosition,
NodeType,
FullPath,
XPath,
Position,
Tree AS TreeView,
Value,
this AS XMLData,
Sort, ID
FROM cte
UNION ALL
SELECT
p.lvl,
x.value('local-name(.)','NVARCHAR(MAX)'),
p.Name,
p.Position,
CAST(N'Attribute' AS NVARCHAR(20)),
p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),
p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),
1,
SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)
+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),
x.value('.','NVARCHAR(MAX)'),
NULL,
p.Sort,
p.ID + 1
FROM cte p
CROSS APPLY this.nodes('/*/@*') a(x)
)
SELECT
ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,
ParentName, ParentPosition,Depth, NodeName, Position,
NodeType, FullPath, XPath, TreeView, Value, XMLData
FROM cte2
I suspect that SQL Server's XQuery implementation is not up to this task, but this is another way of doing it (inspired by this, adapt as required):
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
精彩评论