any xQuery to get all elements' name and full path

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

any xQuery to get all elements' name and full path

Author

Topic

gtan
Starting Member

6 Posts

Posted - 2010-05-27 : 17:23:25

Is there any way to get the name, level and full path of all elements in any given xml hierarchy? so I can check the structure.

I am using SQL server 2005

thanks a lot

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 17:46:05

Check out Peso's blog. He has an awesome query that does that. I'll let you google for it as I don't have the link handy.

Jim

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-27 : 21:24:49

Here http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx
and here http://weblogs.sqlteam.com/peterl/archive/2009/06/04/Extract-XML-structure-automatically-part-2.aspx

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-27 : 21:29:02

[code]DECLARE @Nodes TABLE
(
NodeID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ParentNodeName NVARCHAR(64),
NodeName NVARCHAR(64)
)

DECLARE @Data XML

SET @Data = '
<root>
<elementGroup>
<element>
<stuff>
<comment>Stuff comment</comment>
</stuff>
<comment>Element comment</comment>
</element>
<comment>Element group comment</comment>
</elementGroup>
<comment>Root comment</comment>
</root>'

INSERT @Nodes
(
ParentNodeName,
NodeName
)
SELECT e.value('local-name(..)[1]', 'VARCHAR(MAX)') AS ParentNodeName,
e.value('local-name(.)[1]', 'VARCHAR(MAX)') AS NodeName
FROM @data.nodes('//*[local-name(.) > ""]') AS n(e)

;WITH Yak(NodeLevel, RootName, ElementName, NodeID, NodePath, ElementPath)
AS (
SELECT 0,
ParentNodeName,
NodeName,
NodeID,
CAST(NodeID AS VARCHAR(MAX)),
CAST(NodeName AS VARCHAR(MAX))
FROM @Nodes
WHERE ParentNodeName = ''

UNION ALL

SELECT y.NodeLevel + 1,
n.ParentNodeName,
n.NodeName,
n.NodeID,
y.NodePath + ';' + CAST(n.NodeID AS VARCHAR(MAX)),
y.ElementPath + '\' + CAST(n.NodeName AS VARCHAR(MAX))
FROM @Nodes AS n
INNER JOIN Yak AS y ON y.ElementName = n.ParentNodeName
)

SELECT RootName,
REPLICATE(' ', NodeLevel) + ElementName AS ElementName,
ROW_NUMBER() OVER (PARTITION BY RootName ORDER BY ElementName) AS SortedByElementName,
ROW_NUMBER() OVER (PARTITION BY RootName ORDER BY NodeID) AS SortedByPresence,
ElementPath
FROM Yak
ORDER BY NodePath[/code]

N 56°04'39.26"
E 12°55'05.63"

gtan
Starting Member

6 Posts

Posted - 2010-05-27 : 22:43:32

Thank you so much, Peso, ingenious solutions.

They all work fine with the sample xml data included. But when exec them against my xml data, the first xquery in (Extract-XML-structure-automatically.aspx) runs fine. The other 2 report error as below:

Msg 530, Level 16, State 1, Line 34
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Actually only around 800 elements in my xml data, and 9 levels. But many elements of same name repeat and nest, without an unique ID assigned. Any suggestion?

Is there any internal uid to identify every node in the xml data representation? or is it possible to add an uid for every node ourself?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-28 : 15:23:22

Add
OPTION (MAXRECURSION 0) at the end of the final query.

N 56°04'39.26"
E 12°55'05.63"

gtan
Starting Member

6 Posts

Posted - 2010-05-29 : 07:35:32

I tried OPTION (MAXRECURSION 10000), no help.

The reason is, those nested elements have only one piece of data (element content text) for each element, and duplicates exist.

It is very bad data structure. I have no idea what else I can use to join them to when building the tree.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-30 : 03:19:56

quote:
Originally posted by gtan

Is there any internal uid to identify every node in the xml data representation? or is it possible to add an uid for every node ourself?

No. But you can use ROW_NUMBER() windowed function to generate one for you.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-30 : 03:20:42

Post a link to the xml structure file, and post the expected output.

N 56°04'39.26"
E 12°55'05.63"

gtan
Starting Member

6 Posts

Posted - 2010-05-31 : 22:27:47

Thanks a lot for your help, and sorry for the bother.

I already solved my issue. I was so silly, trying to build the tree hierarchy after I had flatened the xml content into SQL table.

Now I build that tree while xquery from XML itself, then it is very easy to get the context of every node.

Anyway, appreciate the help you already gave to me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources